# How to return multiple columns from one lookup in Excel

Give XLOOKUP a return range several columns wide: `=XLOOKUP(H1,A2:A9,B2:E9)` spills the whole record across.

**Excel:** `=XLOOKUP(H1,A2:A9,B2:E9)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Customer ID | Name | City | Plan | Monthly fee |  | Customer ID | C-2207 |
| C-2201 | Hannah Moore | Denver | Basic | 19 |  | Record |  |
| C-2202 | Victor Silva | Austin | Pro | 49 |  |  |  |
| C-2203 | Mei Tanaka | Seattle | Team | 99 |  |  |  |
| C-2204 | Jonas Berg | Boston | Pro | 49 |  |  |  |
| C-2205 | Fatima Ali | Chicago | Basic | 19 |  |  |  |
| C-2206 | Diego Ramos | Miami | Team | 99 |  |  |  |
| C-2207 | Chloe Martin | Portland | Pro | 49 |  |  |  |
| C-2208 | Samir Haddad | Atlanta | Basic | 19 |  |  |  |

Formula in H2: `=XLOOKUP(H1,A2:A9,B2:E9)`

Result (spills 1 × 4): Chloe Martin, Portland, Pro, $49

## 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.

## Other ways

- `=INDEX(B2:E9,MATCH(H1,A2:A9,0),0)`: 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)`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-return-multiple-columns