Excel XLOOKUP Spill Error: Causes, Fixes, and Examples

An Excel XLOOKUP spill error usually means the formula is returning multiple results but Excel cannot place them in the cells where they need to go. Clear the blocked cells, unmerge cells in the output area, or adjust the formula so it returns only the result you need; the exact fix depends on what XLOOKUP is trying to spill.

What the XLOOKUP spill error means

Excel displays #SPILL! when a formula produces a dynamic array—a result made up of one or more values that should occupy neighboring cells—but the result cannot be placed in the worksheet. XLOOKUP can return a single value, a row or column of values, or several results for several lookup values. A one-value result normally stays in the formula cell; a larger result needs an unobstructed spill range.

For example, a formula that looks up a product ID and returns its name, price, and status needs room in three adjacent columns. If another value, a merged cell, or a table occupies any cell in that output area, Excel cannot display the full result and reports the error. The lookup itself may be perfectly valid: #SPILL! is about placing the result, not necessarily finding a match.

That distinction matters because XLOOKUP can also return errors such as #N/A when it cannot find a match, or #VALUE! in some cases involving incompatible arguments. Those errors have different causes and are not fixed by clearing the spill range.

How XLOOKUP creates an array

The XLOOKUP syntax includes a lookup value, a lookup array, and a return array. The shape of the return array influences the shape of the result. If the return array contains several columns and the match is in one row, XLOOKUP can return those columns across the worksheet. If the lookup value is itself a range containing several values, XLOOKUP can return a result for each lookup value and spill those results down the sheet.

Suppose IDs are in A2:A6, product names in B2:B6, prices in C2:C6, and stock status in D2:D6. With ID 103 in E2, this formula returns the matching name, price, and status from columns B through D:

With IDs in A2:A6, product details in B2:D6, and the ID to find in E2, enter:

=XLOOKUP(E2,A2:A6,B2:D6,"Not found")

If ID 103 is on row 4, the formula returns the three values from B4:D4, spilling horizontally from the formula cell. It will show #SPILL! if the two cells to its right are not available. If you only want the product name, use B2:B6 as the return array instead.

Dynamic arrays are available in Microsoft 365 and Excel 2021 or later, including Excel 2024. XLOOKUP is not available in Excel 2019 and earlier perpetual versions. In those older versions, the function may produce #NAME? rather than a spill error.

Diagnose the blocked spill range

Start by selecting the cell displaying #SPILL!. Excel generally outlines the intended output area, which helps show where results would appear. Click the error indicator next to the formula cell and, when available, choose Select Obstructing Cells. Inspect the highlighted cells and decide whether their contents can be moved or removed.

Check every cell in the outlined spill area, not just the first cell next to the formula. A space, an invisible-looking character, or a formula that displays an empty string can still occupy a cell.
Look for merged cells in the intended output area. An array cannot spill through a merged range, even if the merged cells appear blank.
Check whether the formula is inside an Excel table. Spilled array formulas are not supported within table cells.
Review the formula’s lookup value and return array. A whole-column or unexpectedly large reference may request far more output than intended.
After changing the obstruction or formula, press Enter or recalculate and confirm that the results appear in the expected cells.

On Microsoft 365 for Windows, the error indicator and its options appear beside the selected error cell. Excel for Mac and Excel for the web also show spill errors and identify obstructions, though the appearance and placement of the error menu can differ. The underlying rule is the same: the output area must be available.

Fix cells that block the result

If the spill range is correct, remove or relocate its contents. For example, if a three-column result begins in G2, the cells H2 and I2 must also be available. Clear only the cells that are truly in the way; clearing a cell deletes its content, so move anything important first. If a cell contains a formula returning an empty string, it is still occupied and may need to be moved or removed.

For merged cells, select the relevant range and use Home > Merge & Center > Unmerge Cells. Then make sure the formula’s intended output area is clear. Unmerging can change how existing content is arranged, so check the affected worksheet before applying it across a large range.

Sometimes the best solution is not to clear cells but to move the XLOOKUP formula to a location with enough open space. This is useful on a report sheet where nearby cells contain labels or manually entered values. Leave room for the largest possible result, not merely the result in the current example: a later lookup could return more columns or more rows.

Adjust formulas that return more than intended

Review whether the return array includes unnecessary columns or whether the lookup value includes more cells than intended. Restricting the references can make the result smaller and reduce calculation work. In the earlier example, returning B2:B6 instead of B2:D6 makes XLOOKUP return only the product name. If you need all three fields, keep the wider return array and provide three clear output columns.

Multiple lookup values are useful when you want a list of results. For example, with IDs in A2:A6, prices in C2:C6, and lookup IDs in E2:E4, this formula returns a price for each requested ID:

With IDs in A2:A6, prices in C2:C6, and lookup IDs 103, 101, and 999 in E2:E4, enter:

=XLOOKUP(E2:E4,A2:A6,C2:C6,"Not found")

The output spills down three rows: the price for ID 103, the price for ID 101, and the text Not found for ID 999. Keep the cells below the formula clear. If you meant to look up only the value in E2, change the lookup value to E2 rather than E2:E4.

Full-column references can also create unexpectedly large dynamic results when the formula is designed to process a range of lookup values. Use the actual data range, or a deliberately sized range that will cover the data, instead of asking Excel to return results for an entire column. Avoid changing references blindly, though: too-small ranges can omit valid records.

XLOOKUP inside an Excel table

Excel tables are useful for structured data, automatic expansion, and consistent formulas, but they do not support a dynamic array spilling from a formula cell inside the table. If an XLOOKUP formula inside a table is intended to return several columns or several results at once, place that array formula in ordinary worksheet cells outside the table, with enough blank space for its output.

If each table row should contain one result, use a lookup formula that returns a single value per row. For example, a table column that retrieves one price for each row can be designed as a normal row-by-row calculation rather than as one formula expected to spill an array through the table. Check that the return array is a single column and that the lookup value refers to the current row’s ID. A table may automatically fill a formula down its column, which is different from a dynamic array spilling across multiple cells.

Common misconceptions and related errors

A spill error does not mean XLOOKUP found duplicate matches. By default, XLOOKUP returns the first match it finds; duplicates do not automatically create a list of results. Multiple outputs usually occur because the return array has multiple rows or columns, or because the lookup value contains multiple values. If you need every matching record rather than the first match, XLOOKUP alone is not a general all-matches function; a function such as FILTER may be more suitable in supported Excel versions.

Another misconception is that adding an if-not-found value will resolve #SPILL!. The fourth XLOOKUP argument controls what is returned when there is no match; it does not create space for an array. Similarly, wrapping the formula in an error-handling function can conceal a symptom without resolving why the output cannot be placed. Fix the spill range or change the intended output shape instead.

Also distinguish a genuinely blank cell from a cell that only looks blank. A formula such as one returning an empty string still occupies its cell. Conversely, spaces or other characters may be difficult to notice but block a spill. Clearing the obstructing cell is usually a better diagnosis than repeatedly editing the lookup arguments without checking the worksheet.

When to use another approach

If you need one result, make XLOOKUP return one column or one value. If you need a compact list of matching rows based on a condition, FILTER can be a better fit in Microsoft 365 and Excel 2021 or later, provided its own output area is clear. If a workbook must work in Excel 2019 or an earlier version without XLOOKUP, older formulas such as INDEX with MATCH may be appropriate, but they have different behavior and should be checked for compatibility.

Choose based on the intended output, not just on which formula suppresses an error. A spill is often useful: one formula can populate a whole row or a list of results and update as source data changes. If the worksheet’s design requires fixed cells beside the formula, a single-value lookup may be the better choice. Keep source and output ranges separate where practical, and leave room for dynamic results to grow.

Frequently Asked Questions

Why does XLOOKUP show #SPILL! when the cells look empty?
One of the intended output cells may contain a space, a formula returning an empty string, or another value that is hard to see. Select the error cell and inspect Excel’s outlined output range or use Select Obstructing Cells. Also check for merged cells and for a formula placed inside a table.

How do I stop XLOOKUP from spilling into multiple columns?
Use a return array containing only the column you want. If the formula currently returns a range such as B2:D6, it can return three columns for the matching row. Change the return array to the specific column, such as B2:B6, if only that field is needed.

Can XLOOKUP spill results inside an Excel table?
No. Dynamic array results cannot spill within a table. Put a multi-cell array formula in ordinary worksheet cells outside the table, or redesign the table formula so each row returns one value in its own cell.

Does a duplicate lookup value cause #SPILL!?
Not by itself. XLOOKUP normally returns the first matching record, even if the lookup array contains duplicates. A spill generally means the formula’s inputs call for multiple output cells and Excel cannot place them, not that there are duplicate matches.

Why do I get #NAME? instead of #SPILL!?
#NAME? can appear when Excel does not recognize a function name, including when XLOOKUP is used in Excel 2019 or an earlier version that does not include it. Confirm the Excel version and function spelling. #SPILL! is a dynamic-array placement problem in a version that supports the function.

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.