How to return multiple columns from one lookup in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(H1,A2:A9,B2:E9)Give XLOOKUP a return range several columns wide: =XLOOKUP(H1,A2:A9,B2:E9) spills the whole record across.
- Result on the example
- a 1-row list that spills from H2 in
H2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.
How it works
XLOOKUP returns whatever sits in the return range on the matching row. When that range is four columns wide, B2:E9, it returns four values and they spill across H2:K2. One formula replaces four separate lookups.
=XLOOKUP(H1,A2:A9,B2:E9)Result{1 row × 4 cols} "Chloe Martin", "Portland", "Pro", 49
H1"C-2207"The value in H1.
A2:A98 rows × 1 col: "C-2201", "C-2202", "C-2203", "C-2204", …A range: 8 cells in one column.
B2:E98 rows × 4 cols: "Hannah Moore", "Denver", "Basic", $19, …A range: 8 rows × 4 columns.
XLOOKUP(H1,A2:A9,B2:E9){1 row × 4 cols} "Chloe Martin", "Portland", "Pro", 49XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
H1"C-2207" - lookup_array
A2:A98 rows × 1 col: "C-2201", "C-2202", "C-2203", "C-2204", … - return_array
B2:E98 rows × 4 cols: "Hannah Moore", "Denver", "Basic", $19, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(H1,A2:A9,B2:E9). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 1 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:E9,MATCH(H1,A2:A9,0),0) | spills 1 × 4 | A column number of 0 tells INDEX to return the entire row. It spills in Excel 365 and Google Sheets. |
| =FILTER(B2:E9,A2:A9=H1) | spills 1 × 4 | Returns every matching row, not just the first, if the ID could appear more than once. |
Common mistakes
- The cells to the right must be empty, or the result shows #SPILL!.
- The return range must have the same number of rows as the lookup range.
- In Excel 2019 and earlier, write one lookup per column instead, for example
=VLOOKUP($H$1,$A$2:$E$9,COLUMN(B1),FALSE)filled right.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return multiple columns from one lookup
H2, write one formula that returns the name, city, plan and monthly fee for the customer ID in H1.Questions
How do I return the whole row including the ID?
Start the return range at column A: =XLOOKUP(H1,A2:A9,A2:E9).
How do I return only some of the columns?
Pick them with CHOOSECOLS: =XLOOKUP(H1,A2:A9,CHOOSECOLS(A2:E9,2,5)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.