How to return non-adjacent columns with XLOOKUP in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7))Pick the columns with CHOOSECOLS inside the return range: =XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7)).
- Result on the example
- a 1-row list that spills from J2 in
J2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in J2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7))Result{1 row × 3 cols} "Bread knife", 29, 57
J1"K-318"The value in J1.
A2:A87 rows × 1 col: "K-301", "K-305", "P-210", "P-214", …A range: 7 cells in one column.
A2:G87 rows × 7 cols: "K-301", "Chef knife", "Knives", "Ardent", …A range: 7 rows × 7 columns.
CHOOSECOLS(A2:G8,2,6,7){7 rows × 3 cols} "Chef knife", 59, 34, "Paring knife", …CHOOSECOLS: The columns you name from an array, in the order you name them.- array
A2:G87 rows × 7 cols: "K-301", "Chef knife", "Knives", "Ardent", … - col_num1
2 - col_num2
6 - col_num2
7
- array
XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7)){1 row × 3 cols} "Bread knife", 29, 57XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
J1"K-318" - lookup_array
A2:A87 rows × 1 col: "K-301", "K-305", "P-210", "P-214", … - return_array
CHOOSECOLS(A2:G8,2,6,7){7 rows × 3 cols} "Chef knife", 59, 34, "Paring knife", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7)). 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 |
|---|---|---|
| =HSTACK(XLOOKUP(J1,A2:A8,B2:B8),XLOOKUP(J1,A2:A8,F2:F8),XLOOKUP(J1,A2:A8,G2:G8)) | spills 1 × 3 | Three lookups placed side by side. Longer, but each column is spelled out. |
| =CHOOSECOLS(FILTER(A2:G8,A2:A8=J1),2,6,7) | spills 1 × 3 | 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.
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 non-adjacent columns with XLOOKUP
J2, return the product name, price and stock (columns B, F and G) for the SKU in J1, side by side.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.
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.