XLOOKUP Spill Error: Why It Happens and How to Fix It

An XLOOKUP spill error usually means Excel cannot place all the results returned by the formula into the cells it needs. Clear or move anything blocking the spill range, unmerge cells in that area, or change the formula to return just one value; then check that the formula is not inside an Excel table.

What an XLOOKUP spill error means

In Microsoft Excel for Microsoft 365 on Windows, a spill error appears as #SPILL!. It is a dynamic-array error: the formula has produced, or is expected to produce, results for multiple cells, but Excel cannot display them in the required destination range. The formula may have found the correct match. The problem is often where Excel is meant to put the answer, rather than the lookup itself.

XLOOKUP is commonly used to return one matching value, but it can also return multiple values. For example, the return array can contain several columns, so a single match produces a row of results. Or the lookup value can contain multiple items, so the formula produces multiple answers. Excel places those additional answers in nearby cells, a process called spilling.

The first cell containing the formula is the anchor cell. The cells where its results appear form the spill range. If any cell in that range is unavailable, Excel displays #SPILL! in the anchor cell instead of showing a partial result. You may see a dashed outline marking the expected range.

How XLOOKUP produces results that spill

XLOOKUP uses a lookup value, a lookup array, and a return array. If the return array has more than one column or row, the match can return all of those values. Likewise, if the lookup value is an array of several items, XLOOKUP can return a result for each item. Dynamic-array behavior is available in Excel for Microsoft 365 and Excel 2021 or later; XLOOKUP itself is not available in Excel 2019 or Excel 2016.

For example, with the product code in A2, codes in A5:A10, and product name and price in C5:D10, the formula below returns two values across the row: the matching name and price. If A2 contains P-104, and that row has Desk lamp in C and 18.50 in D, the results are Desk lamp and 18.50.

With the product code in A2, codes in A5:A10, and names and prices in C5:D10, enter this formula in F2:

=XLOOKUP(A2,A5:A10,C5:D10,"Not found")

The formula needs F2:G2 for its two results. If G2 is occupied, the formula cannot spill. It is not necessary for XLOOKUP to be written as a special array formula: in supported Excel versions, dynamic arrays spill automatically.

Find the cell blocking the spill range

Start by selecting the cell showing #SPILL!. Excel often outlines the intended spill range with a dotted border and offers an error indicator beside the cell. If the indicator includes the option Select Obstructing Cells, use it to jump to a blocking cell. This is usually the quickest way to find a visible obstruction.

Look for anything occupying any part of the outlined area. A value, a space, or a formula can block a result. A formula that displays an empty string still occupies a cell, even though it looks blank. Formatting alone—such as a fill color or border—does not usually block a spill. If the obstacle is a cell you need, move the formula or choose a different output area instead of deleting that data.

To clear an unwanted obstruction, select the blocking cell and use Home > Clear > Clear Contents, or press Delete on Windows. On Mac, the Delete key may act as Backspace; use the keyboard’s delete key or the ribbon command as appropriate. Clearing contents preserves formatting, while deleting the cell can affect nearby layout. Check the entire outlined range, not just the cell immediately to the right of the formula.

Common causes and their fixes

Cells already contain data: Clear or move values, spaces, or formulas in the intended spill range. If those entries must stay, move the XLOOKUP formula to an empty area.
Merged cells overlap the range: Unmerge the relevant cells using Home > Merge & Center > Unmerge Cells, or place the formula where its results do not cross merged cells. A spill range cannot be displayed through merged cells.
The formula is inside an Excel table: Dynamic arrays cannot spill from within a table. Put the formula in a blank worksheet area outside the table, or change the return array so it returns a single value per table row.
The result would run off the worksheet: Move the anchor cell so the entire result fits on the sheet. This matters especially when a formula returns a full row or column.
The spill area changes size: A formula that returns a variable-size array may need more room than it did previously. Leave enough clear space for its largest expected result, or use a fixed-size return range where suitable.

Table behavior is a common source of confusion. A table is useful for structured references and automatically filling formulas down a column, but its cells are not a spill area for a multi-cell dynamic-array result. A single-value XLOOKUP in a table column can work normally; the restriction concerns an array result that needs to occupy neighboring cells.

Choose a single-value or multi-value formula

If the task calls for only one field, return only that field. This reduces the chance of spilling and makes the intended output clear. With the lookup value in E2, product codes in A2:A100, and prices in C2:C100, enter this formula wherever one price should appear:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found")

It returns one matching price, or Not found if there is no match. Make sure the lookup array and return array line up: each code in A2:A100 must correspond to the value in the same row of C2:C100. If you do want several fields, select a location with enough clear cells for every returned column.

You can also give XLOOKUP several lookup values. With requested product codes in E2:E3, codes in A2:A4, and stock counts in B2:B4, this formula entered in G2 returns two stock counts vertically. If E2 is P-101 and E3 is P-103, and their counts in B are 12 and 7, the results are 12 and 7 in G2:G3.

With requested codes in E2:E3, the lookup list in A2:A4, and stock counts in B2:B4, enter this formula in G2:

=XLOOKUP(E2:E3,A2:A4,B2:B4,"Not found")

Keep G3 clear for the second answer. If there are more lookup values, leave room for the corresponding number of results. If only one lookup value is intended, check that the lookup_value argument refers to a single cell rather than a range such as E2:E3.

Separate a spill error from other lookup problems

#SPILL! is different from #N/A. XLOOKUP returns #N/A by default when it cannot find a match; its optional if_not_found argument can display a message instead. A spill error concerns placing an array result, not whether the requested item exists. A formula can therefore have a valid match and still show #SPILL! because a destination cell is occupied.

It is also different from a wrong-result problem. XLOOKUP uses exact matching by default, searches from first to last by default, and returns the corresponding entry from the return array. If it returns an unexpected item without a spill error, inspect the lookup value, the lookup and return ranges, duplicate matches, and the match_mode or search_mode arguments. Changing those options will not clear a spill obstruction.

If an older Excel version does not recognize XLOOKUP, that is a compatibility issue rather than a spill problem. Excel 2016 and Excel 2019 may show #NAME? for XLOOKUP. Open the workbook in Microsoft 365 or Excel 2021 or later, or use a compatible alternative such as INDEX with MATCH. Older versions also do not provide the same automatic dynamic-array behavior.

Prevent the error in future workbooks

Before placing a formula that may return an array, decide how many rows and columns it could produce and choose an empty area of that size. Avoid placing the anchor cell immediately beside a table, a report block, or merged headings that might expand into the spill area. If the source list grows, leave enough room for the added output as well.

Return only the needed column or row when one result is sufficient.
Use bounded ranges when practical, rather than returning an entire worksheet row or column without a reason.
Keep spill outputs outside Excel tables; put scalar lookup formulas inside table columns.
When building a report, reserve a separate output section and avoid typing notes or totals inside its expected spill range.

Do not try to fix #SPILL! by copying the formula into every cell where results should appear. A dynamic-array formula belongs in its anchor cell and fills the spill range automatically. Copying it over the destination cells can create the very obstruction preventing the result from appearing.

Frequently Asked Questions

Why does XLOOKUP show #SPILL! when the cells look empty?
One or more cells may contain a space or a formula that returns an empty string. Select the error cell and inspect Excel’s outlined spill range; use Select Obstructing Cells if the option is available. Also check for merged cells and whether the formula is inside a table.

Can XLOOKUP return more than one column?
Yes. If its return array spans multiple columns, XLOOKUP can return the corresponding values across multiple cells. The destination cells must be clear, and the formula must be outside an Excel table for the results to spill.

Does XLOOKUP spill into an Excel table?
No. A dynamic-array formula cannot spill from within a table. Move a multi-cell result formula outside the table, or make the formula return one value for each table row.

Will pressing Delete fix #SPILL!?
It will if the selected cell contains an unwanted obstruction, but it also removes that cell’s contents. Confirm which cell is blocking the range before clearing it. If the cell contains information you need, move the formula instead.

Why does XLOOKUP give #N/A instead of #SPILL!?
#N/A generally means XLOOKUP did not find the lookup value, while #SPILL! means Excel cannot place an array result. Check the lookup range and value for #N/A; check the intended output cells for #SPILL!.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.