Lookup & Reference
XLOOKUP return multiple columns (and reorder with CHOOSECOLS)
Fetch an entire multi-column record in one XLOOKUP call—and select or reorder non-adjacent columns dynamically with CHOOSECOLS.
One multi-column XLOOKUP replaces N separate VLOOKUPs
XLOOKUP can return a multi-column record. Modern Excel also supports array-based alternatives to separate VLOOKUP formulas.
CHOOSECOLS selects and reorders fields from the returned row.
| EmpID (A) | Name (B) | Dept (C) | Salary (D) |
|---|---|---|---|
| E-101 | Nora | Finance | 98000 |
| E-102 | Liam | Ops | 84000 |
| E-103 | Priya | Product | 125000 |
Multi-column XLOOKUP and CHOOSECOLS reordering formulas
Ensure adjacent cells to the right are empty so the returned row can spill cleanly without #SPILL!.
=XLOOKUP("E-103", A2:A4, B2:D4)Select and reorder non-adjacent columns (Salary first, Name second)
CHOOSECOLS(..., 3, 1) picks column 3 (Salary) then column 1 (Name) from B2:D4.
=CHOOSECOLS(XLOOKUP("E-103", A2:A4, B2:D4), 3, 1)Additional worked calculation
Using the initial example data:
=TEXTJOIN(" | ", TRUE, XLOOKUP("E-103", A2:A4, B2:D4))Additional worked calculation
Using the initial example data:
=TEXTJOIN(": ", TRUE, CHOOSECOLS(XLOOKUP("E-103", A2:A4, B2:D4), 3, 1))Additional worked calculation
Using the initial example data:
=COLUMNS(XLOOKUP("E-101", A2:A4, B2:D4))Additional worked calculation
Using the initial example data:
=INDEX(XLOOKUP("E-102", A2:A4, B2:D4), 1, 3)Inputs and interpretation
Keep the destination spill area empty and outside the inputs. Duplicate keys select the first matching row unless a different search mode is specified. TEXTJOIN companions return text in one cell; the unwrapped formulas below return separate cells. Missing keys return an error unless handled explicitly.
Try the example
Use the original values or change them to test your understanding.