To pull a whole record with one lookup — name, department, and salary for an ID, not just one field — point XLOOKUP’s return at several columns. The result spills across the row in a single formula.
The example
Look up an ID and return name, department, and salary together.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Name | Dept | Salary |
| 2 | E-1042 | James | Sales | $71,200 |
| 3 | E-1077 | Priya | Finance | $82,100 |
| 4 | E-1090 | Alex | IT | $79,500 |
The formula
One formula returns the whole record (it spills across three cells):
How it works
A wide return array is the whole trick:
- The lookup works as usual — find
E-1077in the ID column. - The return array
B2:D4is three columns wide, so XLOOKUP returns all three values from the matching row. - Enter the formula once; the result spills right into the next two cells automatically.
- Reorder or pick specific columns by wrapping in CHOOSECOLS (see variations).
Classic INDEX/MATCH does it too, by lookup-ing the row once and letting INDEX return the whole row: =INDEX(B2:D4, MATCH(E2, A2:A4, 0), 0) — the 0 column argument means “all columns.”
Try it: interactive demo
Pick an ID; the whole record is returned at once.
Variations
INDEX/MATCH, whole row
Works in every version (column arg 0):
Pick or reorder columns
Wrap in CHOOSECOLS for just name and salary:
Return a vertical block
If the data runs in columns, return multiple rows the same way.
Pitfalls & errors
#SPILL! The cells to the right of the formula aren’t empty. Clear them so the record can spill.
XLOOKUP multi-column needs Excel 365/2021. Use the INDEX/MATCH version for older Excel.
The return block must align with the lookup. B2:D4 has to cover the same rows as the lookup array A2:A4.
Practice workbook
Frequently asked questions
How do I return multiple columns from a lookup in Excel?
How do I return a whole row with INDEX/MATCH?
How do I return only some of the columns?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class