VLOOKUP Spill Error: Common Causes and How to Fix It

A VLOOKUP spill error (#SPILL!) means Excel is trying to return results into multiple cells but cannot place the complete result in the worksheet. Check whether the formula uses a range of lookup values, then clear or move anything blocking the expected output; if you want one result per row, use a single-cell lookup and fill it down.

What a VLOOKUP spill error means

In Microsoft Excel for Microsoft 365 on Windows, a formula can return an array of values that fills neighboring cells automatically. This is called spilling. VLOOKUP is often used to return one result, but it can return an array when its lookup-value argument contains multiple cells. Excel then needs an empty area, called the spill range, for all the results.

For example, a formula that looks up three IDs at once may return three names in three adjacent rows. The formula itself is entered only in the top cell; the other results are displayed in the spill range and are not separate formulas. If Excel cannot use that range, the formula cell displays #SPILL! instead of the results.

A one-value VLOOKUP normally does not need to spill. If you see #SPILL! beside a VLOOKUP, look for a range or array in the formula, such as E2:E4 instead of E2, or for another part of the formula that produces multiple results. A spill error is about placing an array, not necessarily about whether the lookup value exists in the source data.

Why VLOOKUP can return multiple results

Excel’s dynamic-array behavior allows some functions to process several inputs in one formula. A VLOOKUP whose lookup value refers to a range can therefore look up each value in that range and return multiple answers. This can be intentional: it avoids entering or copying a separate formula for every row.

Suppose A2:B6 contains IDs in column A and names in column B, with 101 mapped to Ava, 102 to Ben, and 103 to Cara. If E2:E4 contains 102, 103, and 101, a formula in G2 can return Ben, Cara, and Ava down the rows. The formula is entered once, in G2, and its results occupy G2:G4 if that area is available.

Array behavior can also arise when a formula is edited, copied from another workbook, or changed from a single-cell reference to a range. In contrast, a normal formula that refers only to E2 is designed to return one result in its own cell. An accidental range reference is a common reason a formula that used to work begins showing #SPILL!.

How to identify what is blocking the result

Start by selecting the cell showing #SPILL!. Excel usually outlines the area where it intended to place the results. That outline gives you a useful clue about the size and direction of the array. A message beside the cell may describe the specific problem, and the error menu may offer an option to select the obstructing cells.

Check the outlined cells for content. A value, a space, or another formula can block a spill, even if a formula displays an empty string and looks blank. Formatting alone, such as a fill color or border, does not normally block the range. Also check for merged cells in the proposed output area: a dynamic array cannot spill through a merged cell.

Consider these common causes:

Occupied cells: One or more cells in the spill range contain data or a formula.
Merged cells: A merged cell intersects the area needed for the results.
An Excel table: Dynamic-array results cannot spill inside a table’s data area.
Worksheet edge: The requested array would extend beyond the last row or column.
Uncertain array size: Some formulas cannot determine the required spill area at calculation time.

For a VLOOKUP with a simple range of lookup values, the intended output size is usually predictable. If the outline is larger than expected, inspect the formula for a broader reference than intended, such as E:E rather than E2:E4.

Ways to fix the error

Choose a fix based on whether you meant to return several results or only one. Before clearing cells, confirm that they do not contain information you need. Clearing an obstruction can remove its contents, so moving the formula to a genuinely empty area may be safer.

Clear the obstructing cells. Select the cells inside the outlined spill range that contain unwanted content and clear their contents. In Excel for Windows, you can select the cells and press Delete. On Mac, select them and use the Delete key; keyboard labeling can differ by keyboard. Do not clear the entire range if it contains data you need to keep.
Move the formula. Place the formula where enough adjacent cells are empty. This is often the simplest option if the current spill range overlaps an existing report or data table.
Unmerge cells in the output area. If a merged cell is in the way, unmerge it or choose a different output location. Review the layout afterward, because unmerging can change how the worksheet looks.
Move an array formula outside an Excel table. Put the formula in a normal worksheet range outside the table’s data area. Tables are useful for structured data and copied-down formulas, but they do not host spilled arrays within their body.
Reduce the lookup input. If you intended to look up only one ID, change a range reference such as E2:E4 to the single input cell E2.
Limit the output size. If the formula references a whole column unnecessarily, replace it with the actual range of lookup values. This avoids requesting more results than you need.

After a fix, Excel recalculates the formula. If #SPILL! remains, select the error cell again and check the new message and outlined range; there may be more than one obstruction.

Example: return several lookup results

Assume A2:A6 contains IDs 101 through 105, B2:B6 contains their corresponding names, and E2:E4 contains 102, 103, and 101. Enter the following formula in G2, with G2:G4 otherwise empty and not inside a table:

=VLOOKUP(E2:E4,A2:B6,2,FALSE)

With those inputs, the results are Ben in G2, Cara in G3, and Ava in G4. FALSE requests an exact match. If any cell in G2:G4 is occupied, Excel cannot display the complete array and G2 may show #SPILL!. A lookup ID absent from A2:A6 would normally produce #N/A for that item; that is a lookup result, not a spill obstruction.

If instead you want to calculate one row at a time, put this formula in G2, with the same source data in A2:B6 and the first lookup ID in E2:

=VLOOKUP(E2,$A$2:$B$6,2,FALSE)

For E2 equal to 102, the result is Ben. The dollar signs keep the source range fixed when you copy the formula down, while E2 changes to E3, E4, and so on. This approach makes each row’s result independent and is useful when the output area already contains other information. It is not a way to make an array spill; it avoids asking Excel to return an array in the first place.

Preventing repeat spill problems

Plan space for array formulas before placing them. Leave the cells to the right or below the formula clear, and avoid putting a spill formula in the middle of a tightly packed report. If the output is part of a formatted Excel table, decide whether a copied-down formula or a separate dynamic-array result is the better layout.

Use the narrowest lookup range that matches the task. Referencing E2:E4 requests three lookup results; referencing E:E can request results for an entire column and may make the intended output impractically large. A bounded range is easier to inspect and less likely to overlap unrelated worksheet content.

When troubleshooting, distinguish cells that merely look empty from cells that are actually empty. A formula returning an empty string, a space character, or a value in a hidden row can still matter. Also, deleting formatting is not a reliable fix for a spill obstruction because formatting is generally not what blocks the array.

Dynamic arrays are available in Microsoft 365 and newer perpetual Excel releases, including Excel 2021. Excel for the web also supports dynamic-array formulas, although its menus and interface can differ from Windows desktop Excel. Excel for Mac supports dynamic arrays too; the same basic checks apply, but keyboard keys and some interface details vary.

Spill errors versus other VLOOKUP errors

#SPILL! means Excel cannot place an array result in the required cells. #N/A usually means VLOOKUP could not find the lookup value, often because the value is absent or the match settings are wrong. #REF! points to an invalid reference, while #VALUE! indicates an argument or value problem. Clearing cells will not fix a missing lookup value, and changing the lookup value will not remove a blocking cell.

VLOOKUP’s fourth argument matters. FALSE requests an exact match; TRUE or an omitted argument uses approximate-match behavior and generally requires the first column of the lookup range to be sorted for dependable results. These match settings affect which value is returned, not whether an array has room to spill. If the cell displays #SPILL!, inspect the output area and formula shape first.

Likewise, a formula can have both a spill obstruction and an underlying lookup issue. Once the spill range is clear, individual results may display #N/A if some keys have no match. Diagnose the errors that remain after Excel can show the full output, rather than treating every error near a VLOOKUP as the same problem.

When to use another lookup approach

If the goal is a single answer per row, a conventional VLOOKUP copied down is straightforward and compatible with older workbooks. If the goal is to process a list of lookup values at once, dynamic-array VLOOKUP can be compact, but its output needs clear space and cannot spill inside a table’s data area.

XLOOKUP is another option in Microsoft 365 and Excel 2021 or later. It can make the return range explicit and can also work with a range of lookup values. For the example where lookup IDs are in E2:E4, source IDs are in A2:A6, and names are in B2:B6, enter this in G2 with G2:G4 empty:

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

For IDs 102, 103, and 101, it returns Ben, Cara, and Ava. The optional fourth argument displays Not found when a key has no match. XLOOKUP does not eliminate spill-space requirements when it returns multiple results; the destination still needs room. For a workbook shared with older Excel versions that do not support XLOOKUP, VLOOKUP may be the more compatible choice.

Frequently Asked Questions

Can VLOOKUP return more than one result in Excel?
Yes. In dynamic-array Excel, giving VLOOKUP a range of lookup values can return a corresponding array of results. The array spills from the formula cell into neighboring cells, provided the output range is available.

Why does my VLOOKUP show #SPILL! when the cells look blank?
A cell may contain a formula that returns an empty string, a space, or other content that is not visually obvious. Select the error cell and inspect the outlined spill range and Excel’s error message. Formatting by itself is not usually the cause.

Can a VLOOKUP spill inside an Excel table?
No. A spilled dynamic-array formula cannot spill within a table’s data area. Put the array formula in a normal range outside the table, or use a single-result formula that can be filled down within the table.

Does #SPILL! mean VLOOKUP cannot find the value?
No. A missing exact-match value usually produces #N/A. #SPILL! means Excel cannot display the array in the cells it needs. Once that is fixed, a missing key may still show #N/A as an individual result.

How do I stop VLOOKUP from spilling?
Use a single lookup cell, such as E2, rather than a range such as E2:E4, if you want only one result. Then copy the formula down for other rows. If you want multiple results, keep the range input and make room for the spill instead.

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.