Excel VLOOKUP #SPILL! Error: How to Fix It Step by Step

Errors & fixes · Updated November 20, 2026

To fix a #SPILL! error in Excel VLOOKUP, first check whether the formula is trying to return results for several lookup values. If it is, clear the cells where those results should appear; if you only need one result, change the lookup argument to a single cell. Then check for merged cells or a formula placed inside an Excel Table. These steps apply to Excel for Microsoft 365 on Windows.

What You’ll Need

  • The workbook containing the VLOOKUP formula and its source data.
  • A moment to inspect the formula cell and the cells beside or below it where results might spill.
  • Permission to clear or move any content blocking the intended results. If the workbook is shared or important, save a copy before changing it.

You do not need an add-in or a special setting to resolve the usual causes. The examples below use a small lookup table: product IDs in A2:A5, product names in B2:B5, and lookup IDs in E2:E4.

Step-by-Step

  1. Confirm that the error is actually #SPILL!. Select the cell showing the error and read the message in the cell or the error indicator beside it. A #SPILL! message means Excel cannot place the formula’s result in the required cells. It is different from #N/A, which means a match was not found, and #REF!, which means a reference is invalid. If you see one of those other errors, the spill-range steps may not address the real problem.
  2. Check whether the formula uses more than one lookup value. Click the formula cell and inspect the first argument inside VLOOKUP, which is the lookup value. A reference such as E2:E4 supplies three lookup values, so a dynamic-array formula in Microsoft 365 can try to return three results. A reference to a single cell, such as E2, asks for one. With IDs in E2:E4 and product IDs and names in A2:B5, this formula entered in F2 asks Excel to return results for all three IDs: =VLOOKUP(E2:E4,$A$2:$B$5,2,FALSE). It should return Notebook, Mug, and Desk lamp in F2:F4, respectively, provided the output area is available. If your intention is a single answer, use E2 instead of E2:E4.
  3. Choose whether you want one result or several. For a single lookup, put the formula in the result cell and refer to only the lookup cell. With IDs in E2:E4 and the lookup table in A2:B5, enter this in F2 to return the name for the ID in E2: =VLOOKUP(E2,$A$2:$B$5,2,FALSE). For the example data, if E2 contains 1002, the result is Notebook. To look up E3 and E4 as well, fill the formula down from F2 through F4; Excel then evaluates one lookup per row. Keep the dollar signs around the source range so that it does not shift as you fill down.
  4. Clear anything occupying the spill range. If you do want several results from one formula, select the formula cell and look for the outlined spill area. Check every cell in that area, not only the first one. A typed value, a space, or even a formula that displays an empty string can block a result. On Windows, select the error cell, click the warning icon, and choose Select Obstructing Cells if Excel offers that option. Review the selected cells before clearing them; move or delete their contents only if they are not needed. Formatting such as fill color or borders does not, by itself, block a spill.
  5. Look for merged cells in the intended output area. A spill cannot occupy a range that includes merged cells. Select the outlined spill area and inspect the ribbon for merged cells. If a merge is blocking the result, select the affected cells and choose Home > Merge & Center > Unmerge Cells. Unmerging can change how a report looks, so check the layout first. Alternatively, move the formula to a clear area that does not overlap merged cells.
  6. Move dynamic-array formulas outside an Excel Table. Excel Tables do not support spilled array results inside their data area. If the VLOOKUP formula is meant to return several results, place it in a regular worksheet range outside the Table, with enough empty cells for the output. If each Table row needs one result, use a single-row reference so the formula returns one value per row. For example, if the current Table has a column named ID and the source list is in A2:B5, this calculated-column formula returns one product name for each row: =VLOOKUP([@ID],$A$2:$B$5,2,FALSE). It will not create a multi-cell spill because [@ID] refers to that row’s ID.
  7. Reduce oversized references and retest. Avoid asking VLOOKUP to process a whole column as a list of lookup values when you only need a short set of answers. A formula that uses E:E could ask Excel to produce an impractically large output. Replace it with the actual input range, such as E2:E20, or use a single-cell reference and fill the formula down. After adjusting the formula or clearing blockers, press Enter and check that the result appears where expected. If you changed a formula that should spill, make sure every intended output cell is visible and that none contains old data.

Common Mistakes

  • Clearing only the visible error cell. The obstruction is often in a later cell in the spill range. Inspect the entire outlined area or use Select Obstructing Cells rather than deleting the formula.
  • Assuming blank-looking cells are truly empty. A cell may contain a space, an old value in white font, or a formula returning an empty string. Select it and check the formula bar before deciding whether to clear it.
  • Changing the source table instead of the output area. The lookup table can be correct while a cell where the answer needs to appear is blocking the spill. Keep the source data intact unless you have identified a separate issue there.
  • Using a multi-cell lookup reference for a one-answer formula. If the formula should return just one name, use a single lookup cell rather than an entire input range. Conversely, do not replace a wanted list of answers with a single-cell reference without considering that it will return only one result.
  • Expecting a spill inside a Table. A dynamic-array formula cannot spill through a Table’s data area. Moving the multi-result formula outside the Table is usually simpler than redesigning the table.

Tips

  • Decide before writing the formula whether the result should be one value per formula cell or several values from one formula. That choice determines whether to use a single lookup reference or a range of lookup values.
  • Leave a clear output area for any formula expected to spill. Avoid placing notes, totals, or other formulas directly below or beside its starting cell.
  • Use an exact match for IDs and other lookup keys when approximate matching is not intended. In the examples, FALSE requests an exact match; it does not prevent or fix a spill error, but it helps ensure the lookup returns the intended item.
  • When copying a single-result formula down, lock the source range with dollar signs and leave the input reference relative. This lets the lookup move from E2 to E3 while continuing to search A2:B5.
  • If you use Excel for Mac, the formulas and the underlying fixes are the same. Ribbon labels are generally similar, though their placement can vary by version. In Excel for the web, use the same checks; some menus and error controls may appear in a different location.

Troubleshooting

The error remains after I cleared the cells. Recheck the complete spill area for a cell that still contains a value, a space, or a formula. Also inspect for merged cells and confirm that the formula is not inside a Table. If Excel provides Select Obstructing Cells, use it again after making changes; a second blocker may be farther down the output range.

The formula returns only one answer, but I expected a list. Check whether the first VLOOKUP argument refers to just one cell, such as E2. To return multiple answers from one formula in Microsoft 365, that argument must refer to the lookup range, such as E2:E4, and the spill area must be clear. Alternatively, keep the single-cell formula and fill it down one row at a time.

The formula now shows #N/A instead. The spill problem may be resolved, but Excel cannot find an exact match for the lookup value. Check that the value exists in the first column of the VLOOKUP table range and that the ID is stored consistently as a number or as text. VLOOKUP searches the first column of its table_array, so verify that the selected range starts with the lookup-key column.

The formula shows #REF! or a different error. Check that the column index number is not greater than the number of columns in the table range, and make sure the source range still exists. A spill fix does not repair an invalid range or a missing match. Address the specific error displayed before changing other parts of the formula.

The output works in a normal range but not in a Table. That is expected for a formula that needs to spill. Keep the multi-result formula in a worksheet area outside the Table, or use a one-row-at-a-time lookup in the Table’s calculated column. If you move the formula, leave enough unobstructed cells for all results.

Frequently Asked Questions

Can VLOOKUP return multiple results in Excel?
In Microsoft 365, VLOOKUP can return an array when its lookup value is a range of values. Excel places the results in adjacent cells if the spill area is available. Older Excel versions may not handle that formula as a dynamic spill, so use one formula per row for compatibility.

Is #SPILL! the same as #N/A?
No. #SPILL! means Excel cannot place an array result in its destination cells. #N/A means the lookup did not find a match. Clear or relocate spill blockers for #SPILL!; for #N/A, check the lookup value and source data.

Do I need to clear an entire column to fix the error?
No. Clear only the cells that obstruct the result range, after checking that their contents are not needed. If the formula references a whole column and is trying to return an enormous array, revise the formula to use the intended input cells instead.

Why did my VLOOKUP start showing #SPILL! when I changed it?
A common reason is that the lookup argument was changed from one cell to a range, so the formula now requests multiple answers. A new value, formula, or merged cell may also have been added to the output area. Inspect the formula and its intended spill range to identify which changed.

Can I put the results in an Excel Table?
You can put individual VLOOKUP results in a Table using a row-specific reference such as [@ID]. A single dynamic-array formula cannot spill its results within the Table’s data area. Put a multi-result formula outside the Table or use a separate lookup formula in each row.

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.