XLOOKUP that returns several columns
When XLOOKUP's return range spans several columns, it returns the whole matching row slice and spills it across the cells.
=XLOOKUP(F2,A2:A100,B2:D100,"Not found")The sheet: A staff table has Employee ID in column A and Name, Department and Salary in B to D. F2 holds the ID you want to look up.
How it works, step by step
- 01F2 is the ID to find.
- 02A2:A100 is the column that is searched.
- 03B2:D100 is the return range, three columns wide.
- 04The match returns the Name, Department and Salary on the same row, spilling across three cells.
- 05If the ID is missing, Not found is shown.
The same logic in four tools
=XLOOKUP(F2,A2:A100,B2:D100,"Not found")=XLOOKUP(F2,A2:A100,B2:D100,"Not found")SELECT name, department, salary
FROM staff
WHERE employee_id = 'E1042';row = staff.loc[staff["employee_id"] == "E1042", ["name", "department", "salary"]].head(1)Mistakes that break it
- !The cells to the right must be empty or you get #SPILL!.
- !The return range must have the same number of rows as the lookup range.
- !XLOOKUP needs Microsoft 365 or Excel 2021. VLOOKUP needs a separate formula per column.
- !A missing ID shows Not found in the first cell only.
Common questions
- How do I keep just two of the columns?
- Use CHOOSECOLS around the result in Microsoft 365, or reduce the return range.
- What if the ID appears twice?
- XLOOKUP returns the first match. Use FILTER to get all of them.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.