XLOOKUP can return more than one cell at a time. If the return range contains multiple columns or multiple rows, Excel can spill the results into neighboring cells automatically.
That matters because it turns one lookup formula into a mini report. Instead of returning only one price or one label, you can return an entire row of related details or a whole column of results from a horizontal lookup.
If you have not covered the basics yet, start with XLOOKUP Function in Excel: Step-by-Step Guide for Beginners. This article focuses on the spill behavior that makes XLOOKUP more powerful than many older lookup formulas.
How Spill Results Work
In modern Excel, some formulas can return more than one value. When they do, Excel spills the results into the empty cells beside or below the formula.
With XLOOKUP, that happens when your return_array contains more than one column or more than one row.
For example, if:
- column
Bcontains employee IDs - columns
C:Econtain employee name, department, and region
then this formula can return all three details at once:
=XLOOKUP(A2,$B$2:$B$20,$C$2:$E$20)
If A2 contains a valid employee ID, Excel spills the matching name, department, and region across three cells.
Return Multiple Columns from One Match
This is the most common multi-return use case.
Suppose your manager types a product code into A2 and wants:
- product name,
- category,
- price,
- supplier
returned in one shot.
If the product codes are in B2:B50 and the return data is in C2:F50, use:
=XLOOKUP(A2,$B$2:$B$50,$C$2:$F$50,"Not found")
This is cleaner than writing four separate lookup formulas. It also reduces the chance that one field references the wrong column while the others still look correct.
If you are still comparing older lookup styles, XLOOKUP vs VLOOKUP in Excel: Which Should You Use? explains why this is one area where XLOOKUP feels much more modern.
Return a Whole Row
You can also return a full row from a table when the lookup key sits in the first column of that data block.
Example:
=XLOOKUP(B10,$B$5:$B$8,$C$5:$F$8)
If B10 contains Central, Excel can return every value from that matching row in C:F.
That is useful for:
- pulling all quarterly sales for one region,
- showing all details for one order,
- retrieving all fields for one customer record.
The key idea is simple: if the return range is a row-wide block, the result can spill horizontally.
Return a Whole Column from a Horizontal Lookup
XLOOKUP can also work across headers. If quarters live across the top row and the data sits underneath, you can return an entire column:
=XLOOKUP(H4,$C$4:$F$4,$C$5:$F$8)
If H4 contains Q3, Excel returns the whole Q3 column from the data block.
That is one reason many users no longer need HLOOKUP as often. XLOOKUP can cover both vertical and horizontal lookup patterns, which makes it easier to standardize on one function.
If you want the older horizontal model explained, see HLOOKUP In Excel: Everything You Need to Know.
Use Nested XLOOKUP for Two-Dimensional Lookups
You can even nest XLOOKUP to handle a vertical and horizontal match together.
For example:
=XLOOKUP(D2,$B$6:$B$17,XLOOKUP($C$3,$C$5:$G$5,$C$6:$G$17))
The inner XLOOKUP finds the matching header or quarter. The outer XLOOKUP finds the matching row label. Together, they return the value at the intersection.
That is the kind of job people often solved with INDEX and MATCH before XLOOKUP became available.
Why Spill Errors Happen
If your formula should return multiple values but only gives an error, check these issues first:
The spill range is blocked
If there is any content in the cells where the result wants to spill, Excel returns a spill error.
The return range does not line up
The lookup range and return range need to match by row or by column. A shape mismatch causes problems fast.
You expected one result but supplied many
This is not always an error. Sometimes the formula is doing exactly what you asked by returning multiple values. The issue is only that the worksheet layout was not prepared for it.
If you are troubleshooting errors specifically, go next to How to Fix Common XLOOKUP Errors in Excel.
When to Use This Pattern
Use multi-return XLOOKUP when:
- you want one formula instead of several parallel lookups,
- your worksheet layout has room for spilled results,
- you want a cleaner summary area or dashboard block.
Avoid it when:
- the output range needs to stay inside one cell,
- the surrounding cells already contain fixed content,
- the workbook depends on older Excel behavior that does not support dynamic array results well.
Microsoft shows the core idea in the official XLOOKUP documentation, including examples that return multiple values. For a formula-first walkthrough, Exceljet's XLOOKUP row or column example is a useful companion.
Verdict
Returning multiple columns and rows is one of the strongest reasons to learn XLOOKUP.
It lets one formula return a full answer block instead of a single cell, which makes modern Excel workbooks simpler and often easier to maintain.