SheetSense

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.

fx
=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

  1. 01F2 is the ID to find.
  2. 02A2:A100 is the column that is searched.
  3. 03B2:D100 is the return range, three columns wide.
  4. 04The match returns the Name, Department and Salary on the same row, spilling across three cells.
  5. 05If the ID is missing, Not found is shown.

The same logic in four tools

Excel
=XLOOKUP(F2,A2:A100,B2:D100,"Not found")
Google Sheets
=XLOOKUP(F2,A2:A100,B2:D100,"Not found")
SQL
SELECT name, department, salary
FROM staff
WHERE employee_id = 'E1042';
Python (pandas)
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.