A VLOOKUP formula shows #SPILL! when Excel is trying to return an array of results but cannot place all of them in the worksheet grid. Clear or move anything blocking the expected output, or change the formula so it looks up one value if you only want one result.
What the VLOOKUP spill error means
In Microsoft Excel for Microsoft 365, formulas can return multiple results and fill adjacent cells automatically. This is called spilling. The cell containing the formula is the anchor cell; the other results appear in a spill range around it. If Excel cannot put the full array in that range, it displays #SPILL! in the anchor cell.
A traditional VLOOKUP takes one lookup value and returns a result from one column of a table. For that common use, it returns a single value and normally does not need to spill. The error is more likely when the lookup value is a range of cells, the column number asks for multiple columns, or the formula is otherwise producing an array. A spill error describes a problem with placing the result, not necessarily a problem finding the lookup value.
This distinction matters because #SPILL! is different from #N/A. The latter usually means an exact match was not found; a spill error means Excel cannot display the array it is calculating. Fixing a blocked output range will not make an unmatched lookup value appear, and changing the lookup range will not necessarily remove a spill obstruction.
How VLOOKUP can return an array
In a dynamic-array version of Excel, giving VLOOKUP several lookup values at once can produce several results. For example, a formula that refers to D2:D4 as its lookup value may return one answer for each of those three cells. Excel places the formula in one cell and attempts to spill the answers down three rows. If the cells below the formula are occupied, the anchor displays #SPILL!.
An array can also result when the column index argument supplies more than one column number. In that case, VLOOKUP may need to return multiple columns horizontally. A formula can therefore have a spill range that extends down, across, or in both directions, depending on its inputs. The shape of the intended range is not always obvious from looking at the anchor cell alone.
With a single lookup value and a single column number, VLOOKUP generally returns one result. For example, with product codes in A2:A5, prices in B2:B5, and the code P-104 in D2, =VLOOKUP(D2,$A$2:$B$5,2,FALSE) returns 12.5. The final argument, FALSE, requests an exact match. This ordinary scalar formula should not need a spill range unless the surrounding formula or context makes it return an array.
Find the expected spill range and its obstruction
Start by selecting the cell with #SPILL!. Excel may show a dotted outline indicating where the results are meant to go. Inspect every cell inside that outline, not just the first cell beneath or beside the formula. A single obstruction can prevent the entire array from appearing.
Common blockers include:
- A value, space, or another formula in one of the output cells. A formula that displays an empty string is still a formula in the cell and can block a spill.
- Merged cells within the intended spill range. Dynamic arrays cannot spill through merged cells.
- An output range that overlaps an Excel table. Dynamic-array results cannot spill inside a table body.
- A result that would extend beyond the worksheet edge, or an array whose size Excel cannot determine in the current calculation.
Formatting by itself, such as a fill color or border, does not usually block a spill. When the error indicator offers an option to identify obstructing cells, use it; otherwise, compare the outlined range with the cells that contain values or formulas. Check for cells that look blank but contain spaces or formulas, too.
Clear the blockage without losing useful data
If the intended output range is correct, remove or relocate its obstruction. Before clearing cells, confirm that they do not contain information you need. You can select the obstructing cells and use Home > Clear > Clear Contents to remove their contents while retaining their formatting. Alternatively, move the formula to an empty area or move the blocking data elsewhere. When the full output area is available, Excel should recalculate and display the results.
Do not try to type over one of the cells in a valid spill range. The cells filled by an array formula are controlled by the anchor formula; edit the anchor instead. If the results are not what you intended, modify the formula or its inputs at that anchor. Also check for merged cells and table boundaries: clearing a value will not solve either of those structural problems.
If a spill would overwrite other worksheet content, it is usually safer to change the formula or choose a new location than to clear the content blindly. For example, a multi-result lookup may be better placed in a dedicated results area. If only one result is required, use one lookup cell rather than a range of lookup values.
Choose between one lookup and multiple lookups
Decide first whether the formula should return one answer or a list. For one answer, point the lookup argument to one cell. With product codes in A2:A5, prices in B2:B5, and P-104 in D2, the formula =VLOOKUP(D2,$A$2:$B$5,2,FALSE) returns 12.5 in the formula cell. If the same formula is filled down one row at a time, each row can look up its own code and return a single result.
For a deliberate multi-lookup, leave enough empty space for every returned result. With product codes in A2:A5, prices in B2:B5, and P-104, P-208, and P-315 in D2:D4, enter =VLOOKUP(D2:D4,$A$2:$B$5,2,FALSE) in E2. In Excel for Microsoft 365, the results spill into E2:E4 as 12.5, 8, and 15. The cells E3 and E4 need to be clear; entering the formula in all three output cells instead of only E2 is not the intended dynamic-array setup.
If your goal is a row-by-row list, another option is to place a single-value VLOOKUP in the first results row and fill it down, adjusting the lookup cell reference for each row while keeping the source range fixed with dollar signs. This approach gives each row its own formula and avoids one large spill range. It can be useful when users need to edit individual results or when the worksheet layout has occupied cells next to the output.
VLOOKUP inside an Excel table
Excel tables are useful for expanding data and copying formulas down, but a dynamic array cannot spill within a table. If a VLOOKUP formula in a table row is set up to return several results, the table context can trigger #SPILL!. Put the array formula outside the table, where its full output area is free, or redesign it to return one value per table row.
A normal one-result lookup in a table is not automatically a spill problem. For instance, if each row of a sales table has one product code and needs one corresponding price, a scalar lookup is a natural fit. Check that the lookup value refers to the current row and that the formula is not using a multi-cell lookup range or multiple column indexes. The table itself is not a reason to replace every VLOOKUP.
Do not confuse spill errors with other VLOOKUP problems
VLOOKUP can produce several different errors, and each points to a different issue. #N/A commonly appears when an exact lookup value is absent or differs from the source because of a typo, extra spaces, or a number-versus-text mismatch. #REF! can occur when the column index asks for a column beyond the width of the table array. #VALUE! can indicate an invalid argument, such as a column index less than 1. These are not spill errors.
Check that the lookup column is the first column in the table array: VLOOKUP searches the leftmost column and returns a value from a column to its right. Confirm that the range includes the intended return column and that the column index counts from the left edge of that range. Use FALSE for an exact-match lookup unless you specifically need approximate matching and have sorted the data as required. These checks can resolve lookup errors, but they do not free a blocked spill range.
If Excel shows #SPILL! even though the formula appears to return one answer, inspect the entire formula for a range reference or an array-valued argument. Also check whether the formula is inside a table. The visible error is about the result Excel is attempting to place, so tracing the formula’s inputs and output shape is more useful than changing the source table at random.
When another lookup approach may help
If you need several return columns, VLOOKUP can be made to return an array in supported Microsoft 365 versions, but its column-number argument and leftmost-column requirement can make the setup less flexible. XLOOKUP, available in Microsoft 365 and Excel 2021 or later, is another option for lookups and can return a corresponding range of results. It can also search in either direction, unlike VLOOKUP’s requirement that the lookup column be at the left of its table array.
Switching functions does not remove the basic spill rule: any formula returning multiple cells needs a clear destination range. If XLOOKUP or another array formula is blocked by occupied cells, Excel can still show #SPILL!. Choose a different function because its lookup behavior suits the task, not as a guaranteed cure for blocked output.
Frequently Asked Questions
Why is my VLOOKUP showing #SPILL! instead of one result?
Excel is treating part of the formula as an array, or the formula is in a context that returns multiple values. Select the cell, inspect the intended spill outline, and check for range-valued lookup inputs, multiple column indexes, table placement, and obstructing cells.
Can blank cells cause a VLOOKUP spill error?
A truly empty cell normally does not block a spill. A cell that looks blank may still contain a space, a value hidden by formatting, or a formula that returns an empty string; those contents can obstruct the output.
Will changing FALSE to TRUE fix #SPILL!?
No. FALSE requests an exact match and TRUE requests approximate matching; neither setting clears a blocked spill range. Changing the match mode can also change which record is returned, so use the mode that matches your lookup task.
Can a VLOOKUP formula spill inside an Excel table?
Dynamic-array results cannot spill inside a table body. Put a multi-result formula in a suitable worksheet range outside the table, or use a one-result formula for each table row.
Is #SPILL! available in older Excel versions?
The dynamic-array behavior described here applies to Microsoft 365 and versions with dynamic-array support, including Excel 2021 and later. Older releases may handle array formulas differently and may not show the same spill behavior or error message.
Related
- RSVP List Template Excel: Build and Track Guest Replies
- Room List Template Excel: How to Build and Use One
- Resource List Excel Template: Set Up, Track, and Share
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- SUMIFS Function Not Working? Fix Common Excel ErrorsIf SUMIFS is returning zero or the wrong total, check that its sum range and criteria ranges cover…
- VLOOKUP #N/A Error: Causes and FixesVLOOKUP returns #N/A when it cannot find the lookup value in the first column of the search range.…
- Excel Cell Background Color: How to Change and Fix ItTo change an Excel cell background color, select the cell or range, then choose Home > Font >…
- Excel Filter Not Working? Fix the Most Common CausesIf an Excel filter is missing rows, showing unexpected results, or not responding, first check that the filter…
- Excel COUNTIFS #VALUE! Error: Causes, Checks, and FixesIn Excel, COUNTIFS returns #VALUE! most often because its criteria ranges are different sizes; make every range cover…
- XLOOKUP Not Working With Numbers? Fix the MatchIf XLOOKUP is not finding a number, first check whether the lookup value and lookup column contain the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.