XLOOKUP Return Multiple Columns

XLOOKUP returns multiple columns in one formula when the return_array argument spans more than one column — instead of writing a separate XLOOKUP per column, give it the whole block and it spills all of them at once. This works in Excel 365 and Excel 2021, where dynamic arrays are native.

How do I make XLOOKUP return more than one column?

Pass a multi-column range as return_array instead of a single column — XLOOKUP finds the matching row once and returns the entire row-slice from that range, spilling it across as many cells as there are columns.

Data (A1:E6):
OrderID  Region  Customer  Product  Amount
1001     North   Acme Co   Widget   500
1002     South   Beta LLC  Gadget   300
1003     North   Acme Co   Gadget   700
1004     South   Beta LLC  Widget   200

=XLOOKUP(1003, A2:A5, C2:E5)
' Spills: Acme Co | Gadget | 700   (3 cells, one formula)

The formula only needs to live in one cell — the two cells to the right stay empty in the formula bar but display the spilled results, marked with a light border in Excel's UI.

Do I need to select the return range differently for multiple columns?

No extra syntax is needed beyond making return_array wider — XLOOKUP doesn't require an array formula, curly braces, or Ctrl+Shift+Enter; it spills naturally because dynamic arrays are the default calculation engine in Excel 365 and Excel 2021.

Single column (returns 1 value):
=XLOOKUP(1003, A2:A5, D2:D5)     ' → Gadget

Multiple columns (spills across cells):
=XLOOKUP(1003, A2:A5, C2:E5)     ' → Acme Co | Gadget | 700

If cells to the right of the formula aren't empty, the formula returns #SPILL! instead of the result — clear that range or move the formula before troubleshooting anything else.

How do I combine this with FILTER to get multiple matching rows?

XLOOKUP always returns exactly one row, even with a multi-column return_array — for every row that matches, not just the first, use FILTER instead, which naturally returns multiple columns for multiple matching rows in one spill.

' Every order from Acme Co, all 3 columns
=FILTER(C2:E5, C2:C5="Acme Co")
' Spills 2 rows x 3 columns:
' Acme Co | Widget | 500
' Acme Co | Gadget | 700

The full syntax, multiple criteria, and the if_empty argument are covered in the FILTER function guide; for filtering with more than one condition using XLOOKUP's boolean-array trick, see XLOOKUP with multiple criteria.

How do I sort the spilled results?

Wrap either XLOOKUP's spilled range or a FILTER result in SORT — it accepts a multi-column array and sorts by whichever column index you specify, keeping all the other columns aligned to the same row order.

' Every Acme Co order, sorted by Amount descending
=SORT(FILTER(C2:E5, C2:C5="Acme Co"), 3, -1)
' sort_index 3 = the 3rd column of the returned array (Amount)
' order -1 = descending

Common pitfalls

Most problems with a multi-column XLOOKUP come from spill-range conflicts or from expecting it to return more than one matching row.

• #SPILL! error: non-empty cells sit in the path of the spill range — clear them or relocate the formula.
• Requires Excel 365 or 2021: Excel 2019 and earlier lack dynamic arrays; you'd need one XLOOKUP or INDEX/MATCH per column instead, each in its own cell.
• Still only the first match: a multi-column return_array doesn't change XLOOKUP's one-row-per-lookup behavior — reach for FILTER when you need every matching row.
• Column order in return_array must match your intended output order: if you reorder source columns later, the spill order silently follows, which can break anything referencing the spilled cells by position.