An INDEX/MATCH formula usually returns one value, so a #SPILL! error means Excel is trying to return results into multiple cells and cannot place them. Clear or move anything blocking the intended spill range, or revise the formula so it returns a single value; the cell’s warning menu can help identify the obstruction.
What an INDEX/MATCH spill error means
In Microsoft Excel for Microsoft 365 on Windows, #SPILL! is a dynamic-array error. It appears when a formula produces several results but Excel cannot display the complete result in the worksheet. The formula cell is the top-left cell of the proposed output, and the other results need room in adjacent cells.
A conventional INDEX/MATCH lookup returns one matching result and ordinarily does not need a spill range. For example, searching for one product code and returning its price should put one value in one cell. If that formula shows #SPILL!, check whether the actual formula differs from the simple pattern, whether it includes an array-valued input, or whether the formula was modified to return a whole row or column.
#SPILL! is not the same as #N/A. #N/A generally means MATCH did not find the lookup value. #SPILL! means Excel cannot place the formula’s result in the required cells. Fixing an obstruction will not make a missing lookup value match, and changing the lookup method will not necessarily clear a blocked spill area.
How INDEX and MATCH produce results
MATCH finds the position of a lookup value within a range; INDEX uses that position to return a value from another range. For a single lookup, the combination is useful because the lookup column and result column need not have a fixed left-to-right arrangement.
With product IDs in A2:A6, prices in B2:B6, and the ID to find in E2, enter this formula in F2:
=INDEX(B2:B6,MATCH(E2,A2:A6,0))
The final 0 asks MATCH for an exact match. If E2 contains the ID on the fourth row of the lookup range, the formula returns the corresponding price from B5. Because E2 contains one lookup value, this example returns one result rather than a vertical array.
In current Microsoft 365, formulas can also calculate with arrays of inputs. With product IDs in A2:A6, prices in B2:B6, and three IDs to find in E2:E4, enter this formula in F2:
=INDEX(B2:B6,MATCH(E2:E4,A2:A6,0))
If all three IDs are found, Excel can return three prices, spilling from F2 down through F4. That is intentional: the formula is producing multiple results. If F3 or F4 is occupied, the formula cannot show the complete answer and returns #SPILL! instead. Dynamic-array behavior is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not calculate this formula the same way.
Find the exact cause in the sheet
Start by selecting the cell that displays #SPILL!. Excel may show a warning icon beside it. Choose the icon and then Select Obstructing Cells, if that option is available. Excel selects cells that prevent the array from appearing, making it easier to spot a value, a formula, or another obstacle.
If the warning menu does not identify the problem, inspect the area where the formula would return results. For a vertical array of three results starting in F2, check F3 and F4 as well as the formula cell. A cell that looks blank may still contain a formula returning an empty string, a space, or other content. Formatting alone, such as a fill color or border, does not normally block a spill.
Also check for merged cells in the proposed output range and whether the formula is inside an Excel table. Dynamic-array formulas cannot spill within table cells. If the spill area crosses a merged cell, or would extend into a table, Excel cannot lay out the results as requested.
Fix a blocked spill range step by step
- Select the #SPILL! cell and review the message in Excel’s warning menu. Use Select Obstructing Cells when available.
- Decide whether the formula should return one answer or several. If only one result is wanted, check for an unintended array reference or a row or column argument that asks INDEX for multiple values.
- If multiple answers are intended, clear or move the contents in the required spill area. Before deleting anything, confirm that the cells do not contain data you need.
- Unmerge cells in the proposed output range, or move the formula to a clear area that does not overlap merged cells.
- If the formula is inside a table, place it in a normal worksheet range outside the table. Alternatively, resize or reposition the table so the expected spill range is clear.
- Recalculate or edit the formula after making the change. Excel should display the results if the range is now available and the formula itself is valid.
To unmerge cells on Windows, select the relevant cells and use Home > Merge & Center. This button toggles merging, so inspect the selected range before clicking it. On Mac, the Home tab also contains Merge & Center. Menu labels can vary slightly by Excel release, but the key point is to remove merging from the intended spill area.
Make the formula return one value
If the goal is a single lookup result, make sure the lookup value is one cell rather than a range. Check references carefully: a reference such as E2:E4 asks the calculation to work with multiple lookup values in supported dynamic-array versions, while E2 is one value. Also review INDEX arguments. In its array form, a row number or column number of 0 can request an entire column or row, which may create a spill.
With values in A2:D10 and a lookup position already calculated in F2, this formula entered in G2 returns the third column of the matching row:
=INDEX(A2:D10,F2,3)
Here F2 must contain one valid row position within the array A2:D10. By contrast, with values in A2:D10, this formula entered in F2 asks INDEX for the entire third column:
=INDEX(A2:D10,0,3)
In a dynamic-array version of Excel, that column can spill from F2 downward. If you wanted just one value, supply a specific row number instead of 0. If you intentionally want the full column, leave enough empty worksheet cells below the formula.
Common spill causes and misconceptions
- Occupied cells: A constant, another formula, or even a formula displaying an empty string can block a result. Clear the obstruction only after confirming it is safe to remove.
- Merged cells: A merged cell in the output area prevents Excel from placing the full array. Unmerge it or choose another location.
- Excel tables: A dynamic array cannot spill inside a table. Put the formula in a regular range outside the table.
- Unexpected array output: A range of lookup values or an INDEX argument requesting a whole row or column can produce more results than expected.
A frequent misconception is that every INDEX/MATCH formula should spill. It should not: a standard lookup with one lookup value and one requested return position produces one value. Another misconception is that blank-looking cells are always empty. A formula that displays nothing can still occupy a cell and block a spill. Conversely, colored or bordered cells are not automatically obstructions if they contain no content and are not merged.
Version and formula considerations
These instructions are for Excel for Microsoft 365 on Windows. Dynamic arrays and #SPILL! behavior are associated with modern Excel; Excel 2021 also supports dynamic-array functions and behavior. In older perpetual versions, formulas that rely on array calculation may require different entry methods or may not be supported. If you share a workbook with someone using an older Excel version, verify that the formula behaves as intended in that version.
On Mac, the underlying formula logic is the same, and the Home tab includes the relevant merge controls. Ribbon placement and some warning-menu details may differ by release. Excel for the web also supports dynamic arrays, although the available controls can differ from the desktop app. When diagnosing, focus first on the output range and formula arguments, not on changing separators or rewriting a valid formula without evidence.
Do not wrap a formula in IFERROR merely to hide #SPILL!. That can conceal the symptom without making the results available, and it may also hide genuine lookup errors such as #N/A. Resolve the blocked range or adjust the requested output first. Use error handling only when the intended result for a genuine error is clear.
Frequently Asked Questions
Can INDEX/MATCH return more than one result?
Yes. In modern Excel, an INDEX/MATCH calculation that receives multiple lookup values or requests a whole row or column can return an array. Excel places those results in adjacent cells when the spill area is available.
Why does my INDEX/MATCH formula show #SPILL! when nearby cells look empty?
A nearby cell may contain a formula returning an empty string or a space, or the output may cross a merged cell or table. Select the error cell and use the warning menu’s Select Obstructing Cells option if available.
Will deleting the cells next to the formula fix the error?
It can, if those cells are genuinely obstructing results you want to keep. Check their contents first. If the formula should return only one answer, revise its references or INDEX arguments instead of clearing unrelated data.
Is #SPILL! the same as #N/A?
No. #SPILL! indicates that Excel cannot display an array result in the required cells. #N/A usually indicates that MATCH could not find the requested lookup value. They have different causes and fixes.
Why can’t a dynamic INDEX/MATCH formula spill in my table?
Excel does not support spilled-array results inside table cells. Move the formula outside the table into an ordinary worksheet range with enough empty space for its results.
Related
- Subtract Dates in Excel to Get Days Between Dates
- Subscript in Google Sheets: Methods That Work
- Subtract Dates in Excel in Years: Two Reliable Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel VLOOKUP Not Working? Fix Formula ErrorsIf VLOOKUP is not returning the result you expect, check the lookup range and use an exact match:…
- Excel Column Names Changed to Numbers? How to Fix ItIf Excel’s column headings show numbers instead of letters, the usual cause is R1C1 reference style. In Excel…
- SUMIFS Not Working With Multiple Criteria? Common FixesWhen SUMIFS is not working with multiple criteria, first check that its arguments are in the right order…
- INDEX MATCH Not Working? Fix Common Excel Lookup ErrorsIf INDEX MATCH is not working in Excel, start by using an exact-match formula and checking that the…
- Excel MID Function Not Working? Causes, Errors, and FixesIf Excel’s MID function is not working, check that its arguments are in the right order, the starting…
- 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…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.