Excel VLOOKUP Spill Error: Causes and Fixes

An Excel VLOOKUP spill error, shown as #SPILL!, means the formula is returning multiple results but Excel cannot place them in the cells it needs. Clear the blocked output cells, or change the lookup input from a range to a single cell if you only want one result.

What a VLOOKUP spill error means

In Microsoft Excel for Microsoft 365, a formula can return an array of results that spills into nearby cells automatically. VLOOKUP normally returns one result for one lookup value, but it can return several results when its first argument is a range or array of lookup values. If even one required destination cell cannot be used, Excel displays #SPILL! in the formula cell.

The error is about where Excel can put the results, not necessarily whether VLOOKUP found the requested items. A formula may be correctly written and still show #SPILL! because a cell in the output area contains a value, a merged cell crosses that area, or the formula is inside an Excel Table. Once the obstacle is removed, Excel can display the results without changing the lookup logic.

How VLOOKUP produces multiple results

Suppose the lookup keys are in A2:A4, and a reference table has IDs in D2:D5 and names in E2:E5. If the IDs are 1001, 1002, 1003, and the corresponding names are Mira, Jon, and Lena, a range of three lookup values can produce three names. With the keys in A2:A4 and the lookup table in D2:E5, enter this formula in G2:

=VLOOKUP(A2:A4,D2:E5,2,FALSE)

The expected results are Mira, Jon, and Lena, spilling down from G2 into G3:G4. The formula is entered once, in the upper-left cell of the intended output area; it is not necessary to copy it down. If G3 or G4 already contains something, Excel cannot place the full result array and reports #SPILL!. Even a space character or a formula that displays an empty string may occupy a destination cell.

By contrast, a single lookup value normally returns a single result. With the lookup key 1001 in A2 and the same lookup table in D2:E5, this formula entered in B2 returns Mira:

=VLOOKUP(A2,$D$2:$E$5,2,FALSE)

The dollar signs keep the lookup range fixed if you copy the formula. The final argument, FALSE, requests an exact match. In older Excel versions, a range supplied as the lookup value may not behave like a dynamic array formula; the automatic spill behavior described here applies to current dynamic-array Excel, including Microsoft 365.

Fix it by checking the spill range

Start by selecting the cell that shows #SPILL!. Excel usually outlines the area where it intends to return the results, which helps identify the range to inspect. Look for anything occupying those cells, including text, numbers, formulas, or cells that appear empty but contain a space.

Check the outlined spill area and move or clear any content that should not be there. Deleting a cell’s contents is different from deleting the cell itself; use care if other data or formatting depends on the layout.
Check for merged cells anywhere in the intended output area. Unmerge them if appropriate, or move the formula so its output does not cross merged cells.
Confirm there is enough room in the worksheet in the direction the results need to spill. A spill cannot extend beyond the worksheet boundary.
Recalculate or edit the formula if needed. Excel should display the returned values when the obstruction is gone.

Clicking the error indicator beside the formula can provide details about the spill problem; in many cases Excel also offers an option to select the obstructing cells. If the area contains useful information, do not clear it just to make the error disappear. Place the formula in an empty region or return to a single lookup value instead.

Common causes beyond occupied cells

A nonblank cell is the most common obstacle, but several other layout conditions can prevent a dynamic result from spilling. A merged cell blocks an output array even if it looks otherwise empty. An Excel Table also does not allow a dynamic array to spill within its data body. If the formula sits in a Table column, Excel may apply calculated-column behavior rather than provide a multi-cell spill range.

Put an array-returning formula in a normal worksheet range outside the Table, with clear cells available for the results. Alternatively, keep the formula in the Table and use a single-row lookup value in each row so every row produces its own scalar result. This is often a better arrangement for data that grows over time: the Table can add a formula to new records, while a separate summary area can hold formulas designed to spill.

Also consider the expected size and direction of the output. A vertical range of lookup values normally produces a vertical set of results; a horizontal array can produce a horizontal set. Leave room in the matching direction. If the range of lookup values changes, the spill area may grow or shrink, so avoid placing fixed notes, totals, or other formulas immediately beside a result that may expand.

Choose between one result and a spilled list

The right fix depends on what the worksheet is supposed to show. If a user chooses one ID and needs one name, make the lookup value a single cell. If the goal is to retrieve names for a list of IDs all at once, preserve the array input and make room for its results. Changing the formula to a scalar lookup can hide the error while silently leaving other requested results out.

For a one-at-a-time lookup, with the selected ID in A2 and the reference table in D2:E5, put this formula in B2:

=VLOOKUP(A2,$D$2:$E$5,2,FALSE)

It returns the name associated with the ID in A2; for 1001, the result is Mira. Copying it down a list works when each row should look up the value from its own A-cell. Keep the table reference absolute so it stays D2:E5 as the formula is copied.

For a list lookup, use the range of IDs as the first argument and keep the entire destination area clear. Avoid filling the spill cells with copies of the formula: a dynamic-array formula is intended to occupy its top-left cell and populate the rest of its result range automatically. If you prefer an individual formula on each row, use a single-cell reference such as A2 and fill that formula down instead.

VLOOKUP spill errors versus other lookup errors

#SPILL! is different from #N/A. The latter commonly means an exact lookup value was not found; it does not, by itself, mean the output cells are blocked. VLOOKUP with FALSE returns #N/A for a key with no exact match. A formula can also return #N/A for one item in an array while other items return matches, depending on the formula and Excel’s displayed results.

#REF! points to an invalid reference, such as a column index that asks VLOOKUP to return a column outside the supplied table range. #VALUE! can indicate an invalid argument or value problem. Those errors call for checking the formula’s arguments and references; clearing cells will not correct them. Read the exact error text before changing the worksheet, because several lookup problems can look similar at first glance.

For VLOOKUP, the column index counts from the left edge of the table range. For example, in D2:E5, column 1 is D and column 2 is E. The exact-match argument is important for IDs, names, and other values that should match precisely. Using approximate matching unintentionally can return an unexpected result, but that is a separate issue from a blocked spill range.

Would XLOOKUP prevent the problem?

XLOOKUP can make lookup formulas easier to read because it separates the lookup range from the return range, but it does not automatically prevent #SPILL!. If its lookup value is an array, it can return an array too, and Excel still needs room to display those results. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not supported in some older Excel releases.

With lookup IDs in A2:A4, IDs in D2:D5, and names in E2:E5, this formula entered in G2 returns the corresponding names in G2:G4:

=XLOOKUP(A2:A4,D2:D5,E2:E5,"Not found")

For the example IDs 1001, 1002, and 1003, the results are Mira, Jon, and Lena. The fourth argument supplies text for an ID that is not found. If G3 is occupied, this XLOOKUP formula can still show #SPILL!; clear or relocate the obstruction just as you would for VLOOKUP. Choose a different function for its lookup behavior or readability, not as a substitute for providing an open spill area.

Frequently Asked Questions

Why does VLOOKUP work in one cell but spill when I use a range?
One lookup value usually produces one result. A range such as A2:A4 supplies several lookup values in dynamic-array Excel, so the formula returns a corresponding array that needs several destination cells.

Can I fix #SPILL! by pressing Enter again?
Usually not. Re-entering the same formula does not remove occupied or merged cells, or make a Table body able to accept a spill. Inspect the outlined output area and address the obstacle or change the formula to use a single lookup value.

Can a formula that looks blank block a VLOOKUP spill?
Yes. A cell containing a formula that returns an empty string is still occupied for spill purposes. A space or other invisible content can also block the result. Check the destination cells rather than relying only on what is visible on screen.

Can I put a spilling VLOOKUP inside an Excel Table?
A dynamic array cannot spill within a Table’s data body. Put the array formula in a regular worksheet range outside the Table, or use a single lookup value in each Table row so the formula returns one result per row.

Does XLOOKUP solve a VLOOKUP spill error?
Not if the cause is blocked output space. XLOOKUP can return multiple results for multiple lookup values, so it needs room to spill too. It may be a convenient alternative for lookup logic, but the output area still has to be available.

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.