# How to return non-adjacent columns with XLOOKUP in Excel

Pick the columns with CHOOSECOLS inside the return range: `=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7))`.

**Excel:** `=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7))` (Excel 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 | I | J |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| SKU | Product | Category | Supplier | Cost | Price | Stock |  | SKU | K-318 |
| K-301 | Chef knife | Knives | Ardent | 21.4 | 59 | 34 |  | Product, price, stock |  |
| K-305 | Paring knife | Knives | Ardent | 6.8 | 19 | 120 |  |  |  |
| P-210 | Saute pan | Pans | Ferro | 18.9 | 49 | 41 |  |  |  |
| P-214 | Stock pot | Pans | Ferro | 24.5 | 69 | 18 |  |  |  |
| K-318 | Bread knife | Knives | Linden | 9.75 | 29 | 57 |  |  |  |
| B-102 | Cutting board | Boards | Oakline | 7.2 | 24 | 88 |  |  |  |
| B-108 | Butcher block | Boards | Oakline | 31 | 89 | 12 |  |  |  |

Formula in J2: `=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7))`

Result (spills 1 × 3): Bread knife, $29, 57

## How it works

CHOOSECOLS builds a three-column table from columns 2, 6 and 7 of `A2:G8`, in the order you list them. XLOOKUP uses it as the return range, so the matching row comes back as product, price and stock, skipping the columns in between.

## Other ways

- `=HSTACK(XLOOKUP(J1,A2:A8,B2:B8),XLOOKUP(J1,A2:A8,F2:F8),XLOOKUP(J1,A2:A8,G2:G8))`: Three lookups placed side by side. Longer, but each column is spelled out.
- `=CHOOSECOLS(FILTER(A2:G8,A2:A8=J1),2,6,7)`: Filter the row first, then pick columns. Returns every matching row if the SKU repeats.

## Common mistakes

- CHOOSECOLS counts columns inside the range you give it, not from column A of the sheet.
- CHOOSECOLS and HSTACK need Excel 2024, Microsoft 365 or Google Sheets. In Excel 2021, write one XLOOKUP per column in three cells.
- The result spills across; keep the cells to the right empty.

## Questions

**Can I choose the columns by header name?**

Yes, with XMATCH: `=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,XMATCH({"Product","Price"},A1:G1)))`.

**How did people do this with VLOOKUP?**

With an array of column numbers, `=VLOOKUP(J1,A2:G8,{2,6,7},FALSE)`, which spills in Excel 365.

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/xlookup-non-adjacent-columns