VLOOKUP returns one column per formula, so you write it again for every column. XLOOKUP can return a whole row or several columns in one formula, and the result spills into the cells next to it.
The idea
The return array is not limited to one column. If you give XLOOKUP a range with three columns, it returns all three.
Example: get name, department and salary
Employee ID in A2:A500, and details in B2:D500. To look up the ID typed in G1:
=XLOOKUP(G1, A2:A500, B2:D500, "Not found")
The three values appear side by side in three cells.
Return only the columns you want
Use CHOOSECOLS (Microsoft 365) to pick columns from the result: =CHOOSECOLS(XLOOKUP(G1, A2:A500, A2:F500), 2, 5) returns columns 2 and 5 only.
Return all rows that match
XLOOKUP returns only the first match. To get every row for a customer, use FILTER instead: =FILTER(A2:F500, B2:B500=G1).
Common mistakes
- The cells to the right must be empty, or you get #SPILL!.
- The lookup column and the return range must have the same number of rows.
- XLOOKUP is not in Excel 2016 or older. Use INDEX and MATCH there.