Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #SPILL! error
Excel onlyWhat #SPILL! means
#SPILL! is an Excel-specific error that appears when a formula tries to output multiple values (a spill range) but one or more of the destination cells are not empty or are otherwise blocked. XLOOKUP returns #SPILL! when it is asked to return multiple columns or rows and the cells below or beside it are occupied.
Common causes
- A cell in the spill range contains data, a space character, or even a formula result — Excel cannot overwrite it, so the entire formula errors.
- The spill range overlaps a merged cell — merged cells block spilling entirely.
- The XLOOKUP is inside an Excel Table (ListObject) and the return_array spans multiple columns, which tables do not support for spill.
- Another spilled array formula already occupies part of the target spill range.
- The return_array returns a dynamic number of rows (e.g. referencing a whole column) and Excel cannot determine the spill range size.
Example fix
Broken
=XLOOKUP(A2,$D$2:$D$100,$E$2:$G$100)Fixed
=XLOOKUP(A2,$D$2:$D$100,$E$2:$G$100,"Not found",0)The fix here is environmental — clear the blocking cells in F2:G2 so the three-column return has room to spill. The formula itself is valid once the range is empty.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Click the XLOOKUP cell and look for the blue dashed border showing the intended spill range. Find and clear any blocking cell — even a space counts. Use Go To Special > Constants to locate invisible content.
- 2Unmerge any merged cells in the spill range: Home > Merge & Center (toggle off) for affected cells.
- 3If the formula is inside an Excel Table and returns multiple columns, move it outside the table or restructure to return one column at a time.
- 4To explicitly reference a spill range that may be blocked, use the # operator on the source: =XLOOKUP(A2,D:D,E:E#) but also resolve the blocking cell first.
- 5If the return_array references a full column and the size is unpredictable, constrain it to a fixed range (e.g. E2:E1000) so Excel can allocate the spill region.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every #SPILL! and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
How do I find which cell is blocking the spill?
Click the #SPILL! cell. Excel highlights the spill range with a dashed blue border and shows 'Spilling into non-empty cell' in the error tooltip. Click the tooltip's 'Select Obstructing Cells' link to jump directly to the blocking cell.
Does Google Sheets have a #SPILL! error?
No. Google Sheets uses a different mechanism for array expansion and does not produce #SPILL!. Blocked spill in Sheets typically shows #REF! or silently truncates the output.
Can I use XLOOKUP inside an Excel Table if it returns multiple columns?
Not directly. Excel Tables do not support multi-column spill formulas within the table body. Place the XLOOKUP formula in a cell outside the table, or use separate XLOOKUP formulas for each return column.
More on XLOOKUP
See all →Reference
XLOOKUP
The modern replacement for VLOOKUP and INDEX/MATCH — look up a value and return a match in any direction.
How-toHow to use XLOOKUP with multiple criteria
Concatenate criteria columns in XLOOKUP's lookup array to match on multiple conditions at once, returning results only when all criteria align.
Error fixFix #N/A
Fix #N/A errors in XLOOKUP in Excel and Google Sheets — lookup value not found, data-type mismatch, incorrect array size, and match mode issues.