One XLOOKUP can bring back a whole row of results instead of a single cell. You don’t need one formula per column: give XLOOKUP a result range that is several columns wide, and the answer spills into the cells to the right. This guide covers adjacent columns, columns in a different order, choosing the column by its header, and looking up several values at once.
The examples use the same table as our other lookup guides, in A1:E9:
| A: Region | B: Product | C: Month | D: Units | E: Price | |
|---|---|---|---|---|---|
| 2 | North | Apple | Jan | 10 | 1.2 |
| 3 | North | Banana | Jan | 5 | 0.5 |
| 4 | South | Apple | Jan | 8 | 1.2 |
| 5 | North | Apple | Feb | 12 | 1.3 |
| 6 | South | Banana | Feb | 7 | 0.6 |
| 7 | East | Cherry | Feb | 3 | 3 |
| 8 | North | Apple | Mar | 15 | 1.25 |
| 9 | South | Apple | Mar | 9 | 1.25 |
Tested in Google Sheets. Every formula on this page was run on November 12, 2026, and the results below are the ones Google Sheets returned. How we test
Adjacent columns
To get the month, units and price of Cherry:
=XLOOKUP("Cherry", B2:B9, C2:E9)
This returned Feb, 3, 3 in three cells side by side. The official XLOOKUP help puts it this way: when the result range has more than one column, the output is the entire row at the matching position.
The result range can even include the column you search in. =XLOOKUP("Cherry", B2:B9, A2:E9) returned the whole row: East, Cherry, Feb, 3, 3.
The result range must cover the same rows as the lookup range. =XLOOKUP("Cherry", B2:B9, C2:E8), one row short, returned #N/A with Array arguments to XLOOKUP are of different size.
Columns in a different order, or not next to each other
To get the price first and then the month, skipping the units, build the result range from separate columns. All four of these returned 3, Feb:
=XLOOKUP("Cherry", B2:B9, HSTACK(E2:E9, C2:C9))
=XLOOKUP("Cherry", B2:B9, {E2:E9, C2:C9})
=XLOOKUP("Cherry", B2:B9, CHOOSECOLS(A2:E9, 5, 3))
=CHOOSECOLS(XLOOKUP("Cherry", B2:B9, A2:E9), 5, 3)
- HSTACK places the ranges side by side, in the order you list them. It’s the easiest to read.
- Curly braces do the same with a shorter formula.
- CHOOSECOLS picks columns by number from a block, counting from its first column: 5 is E and 3 is C in A2:E9.
If you’d rather not count, our lookup formula generator takes column letters such as “E, C” and writes the HSTACK version for you.
#REF!: the result needs empty cells
A multi-column XLOOKUP needs room to spill. We typed an “x” two cells to the right of the formula, and it returned #REF! with Array result was not expanded because it would overwrite data in U31. (U31 was the cell with the x). Clear the cell named in the message, or move the formula.
This also matters when you copy the formula down a column: each copy needs its own empty cells to the right, so put the multi-column lookups in the rightmost part of your sheet.
Found in testing: wrapping a lookup in TEXTJOIN to show the whole result in one cell, =TEXTJOIN(", ", TRUE, XLOOKUP("Cherry", B2:B9, C2:E9)), gives “Feb, 3, 3” and never spills. It’s handy when you only need to read the result, not calculate with it.
Choose the column by its header (two-way lookup)
XLOOKUP can also search across the header row and return a whole column. XLOOKUP("Units", A1:E1, A2:E9) returns column D. Put that inside a second XLOOKUP and you can pick both the row and the column by name:
=XLOOKUP("Cherry", B2:B9, XLOOKUP("Units", A1:E1, A2:E9))
This returned 3. The header can also come from a cell: with “Price” in a cell, the same formula pointing to it returned 3 (Cherry’s price), and with “Month” in the cell it returned Feb. A dropdown in that cell lets anyone switch the column.
Several lookup values at once
This is where XLOOKUP surprised us. With a list of values to look up, it returns one value per item only inside ARRAYFORMULA, and only for a single result column:
| Formula | Result |
|---|---|
=XLOOKUP({"Cherry";"Banana"}, B2:B9, D2:D9) |
Only 3: the second value was ignored |
=ARRAYFORMULA(XLOOKUP({"Cherry";"Banana"}, B2:B9, D2:D9)) |
3 and 5, one per row |
=ARRAYFORMULA(XLOOKUP({"Cherry";"Banana"}, B2:B9, C2:E9)) |
Only Feb and Jan: the units and price columns were dropped |
=BYROW({"Cherry";"Banana"}, LAMBDA(k, XLOOKUP(k, B2:B9, C2:E9))) |
Feb, 3, 3 and Jan, 5, 0.5: complete rows |
None of the incomplete results gave an error, so check the size of the output. In a real sheet the values are usually in a column, such as H2:H20: use BYROW(H2:H20, LAMBDA(k, XLOOKUP(k, B2:B9, C2:E9))), or put =XLOOKUP(H2, $B$2:$B$9, $C$2:$E$9) in the first row and copy it down. We tested both; the dollar signs keep the data ranges fixed when you copy.
Vertical instead of horizontal
To list the results down a column instead of across, wrap the lookup in TRANSPOSE: =TRANSPOSE(XLOOKUP("Cherry", B2:B9, C2:E9)) returned Feb, 3 and 3 in three cells down.
With several conditions
The wide result range also works with the multiplied-conditions method, inside ARRAYFORMULA: =ARRAYFORMULA(XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), C2:E9)) returned Jan, 10, 1.2. Why ARRAYFORMULA is required here is explained in XLOOKUP with multiple criteria.
Doing the same with VLOOKUP
VLOOKUP can return several columns if you give it a list of column numbers, but only inside ARRAYFORMULA. =ARRAYFORMULA(VLOOKUP("Cherry", B2:E9, {2, 3, 4}, FALSE)) returned Feb, 3, 3. Without ARRAYFORMULA, the same formula returned only Feb, with no error. That is one more reason to prefer XLOOKUP for this job; the full comparison is in VLOOKUP vs XLOOKUP vs INDEX MATCH.