How to return multiple columns with XLOOKUP

One XLOOKUP, several columns

The catalog has the SKU in column A and five details beside it. You want everything about the SKU typed in H2. Instead of pointing return_array at one column, point it at all of them: =XLOOKUP(H2,A2:A9,B2:F9). Excel finds KT-208 in row 4 and returns that row of B:F, so the formula cell shows "Cast iron skillet" and the four cells to its right fill with Cookware, 48, 15 and Ironhill.

You type the formula once, in the first cell only. The other four cells are a spill range: they show a faint blue border when selected, and their formula appears greyed out in the formula bar. You cannot type over or delete one of them on its own. To change the result, edit the first cell.

This needs dynamic arrays, so Excel 365, Excel 2021, Excel 2024 or Excel for the web. Older versions do not have XLOOKUP at all; see the INDEX/MATCH section below.

Pick non-adjacent columns or change their order with CHOOSECOLS

Often you want only some of the columns, say the product, the stock and the supplier, but not the category or price. Wrap the return range in CHOOSECOLS and list the column numbers you want, counted from the left edge of that range: =XLOOKUP(H2,A2:A9,CHOOSECOLS(B2:F9,1,4,5)) returns Cast iron skillet, 15 and Ironhill.

The numbers also set the order, so CHOOSECOLS(B2:F9,5,1) puts the supplier first and the product second. CHOOSECOLS is in Excel 365 and Excel 2024; it is not in Excel 2021. In 2021, use one XLOOKUP per column instead (next section).

Counting columns by number has the same weakness as VLOOKUP: if someone inserts a column inside B:F, column 4 becomes a different field. If the layout changes often, look the column up by its header, as shown further down.

One XLOOKUP per column (works in every XLOOKUP version)

If you want the results laid out under your own headings, in any order, with gaps between them, write one XLOOKUP per output cell. The trick is the dollar signs: lock the SKU cell and the lookup column, and leave the return column unlocked so it moves when you fill right.

=XLOOKUP($H$2,$A$2:$A$9,B2:B9) returns the product. Fill it one cell right and the last range becomes C2:C9 (category), then D2:D9 (price), and so on. If you also want to fill down for a list of SKUs in H2:H6, use $H2 and B$2:B$9 so the SKU row moves but the return range stays on rows 2 to 9.

This version never causes #SPILL!, and each cell keeps its own number format. It is the safer choice inside an Excel table, because spilling formulas are not allowed in tables.

Return array the wrong size gives #VALUE!

XLOOKUP needs the return array to have exactly as many rows as the lookup array. Here the lookup column stops at row 9 but the return range runs to row 10, one row too many, so Excel returns #VALUE! instead of guessing how the two line up.

This usually happens after you add rows and only update one of the ranges, or when you type the ranges by hand. Select the formula, press F2 and check that the coloured boxes start and end on the same rows. Using full columns on both sides (A:A and B:F) or an Excel table keeps them in step automatically.

#SPILL!: something is in the way

A multi-column XLOOKUP needs every cell it spills into to be empty. If any of them holds a value, a formula, a space or even an empty string from another formula, the whole result is replaced by #SPILL!. Click the error and select the warning icon: Select Obstructing Cells takes you to the culprit.

The other common cause is the table rule. Dynamic array formulas that spill are not supported inside an Excel table, so a multi-column XLOOKUP typed into a table column always shows #SPILL!. Put it outside the table, or use one XLOOKUP per column as above.

1. Click the cell showing #SPILL!.
2. Click the yellow warning icon next to it and choose Select Obstructing Cells.
3. Delete or move whatever is in those cells (press Delete, not the space bar).
4. The row spills as soon as the range is clear.

Choose columns by header, and the INDEX/MATCH fallback

To return the column named in a header cell, so the formula survives inserted columns, match the header as well as the SKU. With INDEX and MATCH that is a two-way lookup: the first MATCH finds the row, the second finds the column. The same idea with XLOOKUP is to nest one inside the other: =XLOOKUP($H$2,$A$2:$A$9,XLOOKUP(I$1,$B$1:$F$1,$B$2:$F$9)), where I1 holds a header such as Supplier.

INDEX/MATCH also works in Excel 2019 and earlier, which have no XLOOKUP. In those versions use one formula per column, such as =INDEX(B$2:B$9,MATCH($H$2,$A$2:$A$9,0)) filled right. In Excel 365 you can return the whole row at once with =INDEX(B2:F9,MATCH(H2,A2:A9,0),0); the 0 for the column means all columns, and it spills like XLOOKUP does.

XLOOKUP returns the first match only: use FILTER for all of them

XLOOKUP stops at the first row that matches. That is right for a unique key like a SKU, but if you look up a supplier, you get one product even when the supplier has several. Ironhill makes both the skillet and the Dutch oven.

FILTER returns every matching row, with as many columns as you give it. It spills down and across, so leave room in both directions.