XLOOKUP to Return Multiple Columns at Once

To make XLOOKUP return multiple columns, select several columns as the third argument, the return array. =XLOOKUP(G2,A2:A6,C2:E6) finds an ID and brings back three values at once, which spill into the cells to the right. This guide shows that basic pattern, then how to pick columns out of order, total them, and fill the formula down for a list of IDs.

All the examples use this quarterly sales table in A1:E6:

XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. If you are new to it, start with the XLOOKUP guide.

How to return multiple columns with one XLOOKUP#

XLOOKUP has three required parts: what to find, where to look, and what to return. The return array can be one column or several. With R103 in G2, type this in H2:

=XLOOKUP(G2,A2:A6,C2:E6)

Excel finds R103 in row 4 of the table and returns that row's values from C, D and E: 16000 in H2, 12000 in I2 and 15000 in J2. You typed one formula, but three cells fill. This is called spilling. Click I2 and the formula bar shows the formula greyed out, because it lives only in H2.

To return the name as well, widen the return array to B2:E6. The result then spills across four cells: Lena, 16000, 12000, 15000. The only rule is that the return array has the same number of rows as the lookup array. The XLOOKUP function guide lists every argument.

How to fix #SPILL! when returning several columns#

A #SPILL! error means the results have nowhere to go. XLOOKUP needs every cell in its spill range to be empty. If J2 holds a note, the H2 formula shows #SPILL! and returns nothing. Fix it by:

• Clearing the blocking cell. Click the error and Excel outlines the spill range with a dashed border so you can see which cells it needs.
• Moving the formula somewhere with enough empty cells to its right.
• Moving it out of an Excel table. Spilled formulas can't sit inside a table.

How to return columns that are not next to each other#

Sometimes you want the name and Q3 only, skipping Q1 and Q2. In Microsoft 365 or Excel 2024, stack the columns you want into one return array with HSTACK:

=XLOOKUP(G2,A2:A6,HSTACK(B2:B6,E2:E6))

This returns Lena and 15000, side by side. You can list the columns in any order: HSTACK(E2:E6,B2:B6) puts Q3 first. Another option is CHOOSECOLS, which picks columns by number from the full result: =CHOOSECOLS(XLOOKUP(G2,A2:A6,B2:E6),1,4) gives the same two values.

In Excel 2021, which has neither function, write one XLOOKUP per column instead. It is more typing, but it works.

How to look up a column by its header#

If you want one value but the column changes, look up the header too. Put an XLOOKUP inside the return array:

=XLOOKUP(G2,A2:A6,XLOOKUP("Q2",C1:E1,C2:E6))

The inner XLOOKUP finds "Q2" in the headers and returns that whole column, D2:D6. The outer one then finds R103 in it: 12000. Replace "Q2" with a cell, such as H1, and copy the formula across under headers Q1, Q2 and Q3 to rebuild the row. Lock the ranges first: =XLOOKUP($G2,$A$2:$A$6,XLOOKUP(H$1,$C$1:$E$1,$C$2:$E$6)).

How to sum or join the returned columns#

Wrap the result in another function to turn several values into one answer. Nothing spills, because the outer function returns a single value:

The guide to returning multiple values from a lookup covers the opposite case: many matching rows rather than many columns.

How to fill the formula down for several IDs#

To return several columns for a list of IDs, write the formula once and fill it down. Say G2:G4 hold R101, R103 and R105. In H2, lock the table ranges with $ (press F4 on Windows, or Cmd+T on a Mac, while the cursor is on the range):

=XLOOKUP(G2,$A$2:$A$6,$C$2:$E$6)

Fill down to H4 (Windows: Ctrl+D; Mac: Cmd+D). Each row spills its own three values. G2 has no $, so it changes to G3 and G4 as you fill, while the table stays fixed.

If an ID might be missing, add a fourth argument: =XLOOKUP(G2,$A$2:$A$6,$C$2:$E$6,"Not found"). When there is no match, you get one "Not found" instead of three columns. When the match needs two conditions, such as region and product, see XLOOKUP with multiple criteria.

XLOOKUP vs VLOOKUP for multiple columns#

VLOOKUP can only return one column per formula, because its column number is a single value. To get three columns you need three formulas, each with a different column number, or an array trick that only works in newer Excel. XLOOKUP does it with one range. XLOOKUP also keeps working when someone inserts a column into the table, because it uses ranges instead of a column number. A VLOOKUP with a hard-coded 3 quietly starts returning the wrong column. If your files must open in Excel 2019 or earlier, the guide to returning several columns with VLOOKUP shows the older methods.

Practise it#

Practise XLOOKUP in a live spreadsheet: you type each lookup and it checks your answer instantly. Then try the exercise on this page, and move on to the modern Excel functions lesson to practise spilling formulas like FILTER, UNIQUE and SORT.