One lookup, many columns. Point XLOOKUP’s return argument at several columns and it spills the matching row’s whole record — no repeated lookups, no column-index counting.
The example
One ID lookup returns the full record.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Region | Sales |
| 2 | Ann | East | 95 |
The formula
Return several columns at once:
How it works
A multi-column return range spills the record:
- Use XLOOKUP as usual — lookup value, lookup array — but make the return array several columns wide.
- XLOOKUP returns the matched row across all those columns, spilling into the neighbors.
- To reorder or pick specific fields, wrap with CHOOSECOLS:
CHOOSECOLS(XLOOKUP(…), 3, 1). - This replaces three separate VLOOKUPs (or INDEX/MATCH calls) with one clean formula.
Horizontal or vertical. If your return range is a single wide row, XLOOKUP spills horizontally; a tall block spills vertically. Combine with HSTACK to assemble a custom record from non-adjacent columns.
Try it: interactive demo
Look up an ID; return the whole row.
Variations
Reorder fields
Pick columns:
Two-way lookup
Row and column match:
If not found
Default record:
Pitfalls & errors
Spill space. A multi-column return needs empty neighbor cells, or #SPILL!.
365/2021 for XLOOKUP. Older Excel uses INDEX/MATCH; a multi-column INDEX return also spills in 365.
Return range orientation. Match the return block’s shape to how you want it to spill (row vs column).
Practice workbook
Frequently asked questions
How do I return multiple columns with XLOOKUP?
How do I reorder the returned fields?
Why do I get a #SPILL! error?
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