Excel VLOOKUP Spill Error: Causes and Fixes

Errors & fixes · Updated November 20, 2026

A VLOOKUP spill error usually means Excel cannot place all the results a formula is trying to return into the cells beside or below it. In Microsoft 365 for Windows, check whether the VLOOKUP uses a range of lookup values or requests several return columns, then clear or move anything blocking the intended spill range. The official error label is #SPILL!; “spill error” is an informal way to describe it in a VLOOKUP context, not a separate VLOOKUP error code.

What “spill error” means in Excel

Excel uses the term “spill” for a dynamic-array formula’s results appearing in neighboring cells automatically. The cell containing the formula is the anchor cell, and the cells where its additional results appear make up the spill range. When Excel cannot display the full result, it shows #SPILL! in the anchor cell. The formula may be valid; the problem is often the space available for its output.

“Spill error” is not the formal name of a distinct function or error category. It is an informal, descriptive phrase people use when a formula—here, VLOOKUP—produces #SPILL!. The wording comes from Excel’s dynamic-array behavior: results “spill” into adjacent cells. Microsoft uses the error label #SPILL! and the related terms spill range and spilled array. You might say, “My VLOOKUP has a spill error because a value is blocking the result cells.”

This distinction matters because VLOOKUP itself is not necessarily broken. A VLOOKUP that searches for one value and returns one value normally occupies one cell. A VLOOKUP that receives multiple lookup values, or is asked for several return columns, can produce an array that needs room to expand.

Why a VLOOKUP can return multiple results

VLOOKUP has four arguments: the value to find, the lookup table, the number of the return column, and whether to use an approximate or exact match. In a traditional single-value formula, the first argument is one cell and the column number is one number. In Microsoft 365, a range supplied as the lookup value can instead ask Excel to perform several lookups and return the results as a dynamic array.

For example, if the lookup value refers to A2:A4, Excel can look up the three values and return three results down the sheet. Likewise, an array of column numbers can request more than one column from the lookup table, so results may spill across columns. This is normal dynamic-array behavior in Microsoft 365 and Excel 2021 or later; it does not mean VLOOKUP has changed into a different function.

Spilling requires open worksheet cells. Excel will not overwrite existing data to make room, because doing so could destroy it. A spill error therefore often appears after someone changes a single-cell lookup into a range lookup, expands the return values, or enters a formula where nearby cells are already occupied.

A concrete VLOOKUP example

Suppose A2:A4 contains the product IDs 102, 104, and 105. The lookup table in D2:E6 contains IDs in column D and names in column E: 101 is Oak, 102 is Elm, 103 is Ash, 104 is Maple, and 105 is Pine. With the formula in G2 and G3:G4 empty, Excel can return all three names.

With IDs in A2:A4 and the ID-to-name lookup table in D2:E6, enter this in G2; the expected results in G2:G4 are Elm, Maple, and Pine:

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

If G3 already contains a note, a number, or another formula, Excel cannot place the second result there and may show #SPILL! in G2. The lookup table and exact-match setting are not the cause; the occupied output cell is. Clear the obstruction only if it is safe to do so, or choose a different location for the formula.

A VLOOKUP can also return more than one column when its column-number argument is an array. For instance, if A2 contains 104 and D2:F6 has IDs, product names, and regions in that order, the following formula in G2 returns Maple and West across G2:H2, assuming the row for ID 104 contains those values:

With the lookup value in A2 and the three-column table in D2:F6, enter this in G2; the result is Maple in G2 and West in H2:

=VLOOKUP(A2,D2:F6,{2,3},FALSE)

If H2 is occupied, the two-column result cannot spill fully. In both examples, remove or relocate the obstacle, or change the formula so it returns only the result you actually need.

Common causes of #SPILL! with VLOOKUP

  • Occupied output cells: A value, a space, or another formula in the spill range can block the result. A formula that displays an empty string still counts as content for this purpose; it is not the same as a genuinely empty cell.
  • A merged cell in the output area: Dynamic-array results cannot spill through merged cells. Unmerge the relevant cells or put the formula and results in an unmerged area.
  • The formula is inside an Excel Table: Spilled array formulas are not supported within table cells. Put the formula outside the table, or use a conventional single-result formula in a table row.
  • The formula returns more than intended: A lookup-value reference such as A2:A100 requests many lookups. If you meant to look up just the value on the current row, the larger range may be accidental.
  • The requested output cannot fit: A very large array can run into the edge of the worksheet or an obstructed part of the destination range. Reduce the lookup range or place the formula where its complete result can fit.

Excel’s error message can help identify the specific obstruction. Select the cell showing #SPILL! and use the error indicator that appears beside it to inspect the message. The details vary by cause; an occupied-cell message, for example, points you toward the spill range rather than toward the lookup table.

How to fix the error safely

  1. Check what the formula is asking for. Select the error cell and inspect the formula bar. Look for a lookup-value range such as A2:A4 or an array of column numbers. Decide whether multiple outputs are intentional.
  2. Find the intended spill range. Estimate how many rows or columns the formula needs. Check those cells for data, formulas, merged cells, or a table boundary. Do not delete anything until you know it is safe to remove or move.
  3. Make room or relocate the formula. Clear genuine obstructions, unmerge cells if appropriate, or move the formula to a blank area outside the table. If the values need to remain, put the formula somewhere else rather than overwriting them.
  4. Use a single lookup when that is the goal. With the lookup value in A2 and the table in D2:E6, enter this in G2; if A2 is 104, it returns Maple. Copying it down one row at a time makes each row return one result:

=VLOOKUP(A2,D2:E6,2,FALSE)

Changing a range reference to a single-cell reference is not a universal fix: it changes the calculation from several lookups to one. Use it only when one result per formula cell is what you intended.

How to distinguish spill errors from lookup errors

#SPILL! concerns where an array result can go. It is different from #N/A, which commonly means an exact lookup value was not found, and from #REF!, which can indicate an invalid reference or an invalid VLOOKUP column number. A misspelled ID or a lookup table with the wrong first column can cause a lookup problem, but those issues do not by themselves explain a blocked spill range.

Adding an error wrapper is not a reliable way to fix a spill obstruction. A wrapper may handle a missing-match result, but it does not create empty destination cells for an array that must spill. First identify whether the problem is the output range or the lookup itself; then address that specific cause. This also avoids hiding useful errors while leaving the underlying formula unchanged.

Alternatives and ways to avoid repeat problems

If you only need one lookup result per row, keep the lookup value as a single-cell reference and copy the formula down. That pattern makes the output location predictable and fits naturally in a table. If you want one formula to process several lookup values at once, keep the range reference, but reserve enough blank rows or columns for its results and place the formula outside an Excel Table.

XLOOKUP is another option in Microsoft 365 and Excel 2021 or later. It can return a corresponding value or range, but it can also spill when given multiple lookup values or when returning multiple columns. Switching to XLOOKUP does not solve a blocked spill range automatically; the output still needs space. Choose a lookup function based on the lookup layout and Excel versions used by the people opening the workbook, not merely to suppress an error.

For a workbook that will be shared, leave visible space beside and below dynamic-array formulas, label the output area, and avoid placing notes in cells that may later become part of a spill range. When a formula sits near changing data, recheck its output area after inserting rows or adding formulas. On Mac and Excel for the web, the underlying dynamic-array principle is the same, though the interface and exact error-message controls may differ from Excel for Microsoft 365 on Windows.

Frequently Asked Questions

Is “spill error” an official Excel term?

It is informal wording, not the official name of a separate error. Excel’s error label is #SPILL!. People say “spill error” as shorthand for that error when a dynamic-array formula, such as a VLOOKUP, cannot display all its results.

Why does my VLOOKUP show #SPILL! when I only want one answer?

Check whether the first argument refers to a range rather than one cell, or whether the column-number argument requests multiple columns. If you intend one answer, use a single lookup-value cell and a single column number, then make sure the formula is not inside a table in a way that requires a spilled result.

Do I need to delete the cells below the VLOOKUP?

Only if those cells contain data you are certain can be removed. Excel does not overwrite them automatically. Move the data or the formula instead if the contents matter, and check the full spill range rather than clearing cells at random.

Can a VLOOKUP spill into an Excel Table?

Dynamic-array formulas cannot spill within Excel Table cells. Place the formula outside the table if it needs to return multiple results, or use a single-result lookup formula in the appropriate table row.

Will XLOOKUP remove a VLOOKUP spill error?

Not necessarily. XLOOKUP can return arrays too, so it also needs an unobstructed spill range when returning multiple results. It may be a useful alternative for other lookup reasons, but first clear or relocate whatever blocks the intended output.

Related

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.