To fix an Excel XLOOKUP #SPILL! error, select the cell showing the error, use the warning icon to identify what is blocking the result, and clear or move that content. If the formula is meant to return several values, make sure its entire spill range is empty and that the formula is outside an Excel table. XLOOKUP can return more than one cell when its return range contains multiple rows or columns, so the error often means Excel has nowhere to place the full result.
The steps below are for Microsoft Excel for Microsoft 365 on Windows. XLOOKUP and dynamic array spill behavior are also available in Excel 2021. Excel for Mac and Excel for the web have similar error indicators and formula behavior, though ribbon labels and some menus may look slightly different.
What You’ll Need
- The workbook containing the XLOOKUP formula and permission to edit the cells around it.
- A basic understanding of which value you are looking up and which column or columns should be returned.
- Excel for Microsoft 365 or Excel 2021 for XLOOKUP and dynamic array results. Older Excel versions may not support XLOOKUP.
Step-by-Step
- Identify the formula and intended result. Select the cell with #SPILL! and look at the formula bar. Note the lookup value, lookup array, and return array. XLOOKUP can return a single value, such as one price, or a dynamic array, such as a price and stock count from adjacent columns. Deciding which result you want helps distinguish a genuine blockage from a formula that returns too much. Do not delete nearby data until you know whether those cells are meant to contain the lookup result or existing worksheet information.
- Use Excel’s error indicator to locate the obstruction. Click the #SPILL! cell. If Excel displays a warning icon beside it, click the icon and read the message. For a blocked spill range, choose Select Obstructing Cells if that option is available. Excel outlines the cells preventing the result from appearing. The intended spill area may also be shown with a dashed border when you select the formula cell. On Mac or Excel for the web, use the error message or select the formula cell and inspect the outlined spill area; the exact menu presentation can differ.
- Clear only the cells that should be empty. A cell blocks a spill if it contains a value, a formula, or even a space. Select an obstruction and check the formula bar before removing anything. If the content is not needed, press Delete to clear it; deleting the cell’s contents preserves the worksheet layout. If the cell contains important data, move the formula to an open area or move the data elsewhere rather than overwriting it. Formatting alone generally does not block a spill, so clearing formats is usually not the fix.
- Check the full width and height of the result. A multi-column return array needs empty cells to the right of the formula; an array of lookup values or a return array spanning multiple rows can need empty cells below it. For example, with IDs in A2:A4, prices in C2:C4, stock counts in D2:D4, and the lookup ID 102 in F2, enter this formula in G2:
=XLOOKUP(F2,A2:A4,C2:D4,"Not found"). If row 3 has ID 102, price 250, and stock count 18, the result fills G2:H2 with 250 and 18. Both cells must be available. If H2 contains a note, the formula can show #SPILL! even though G2 is empty. - Decide whether you need one result or several. If the formula should return only a price, make the return array just the price column. With IDs in A2:A4, prices in C2:C4, and the lookup ID 102 in F2, enter this in G2:
=XLOOKUP(F2,A2:A4,C2:C4,"Not found"). If row 3 contains the matching ID and its price is 250, G2 returns 250 as a single value. This is preferable to clearing cells across the sheet when you do not actually need multiple output columns. Check that the lookup and return ranges cover corresponding records. - Move the formula outside an Excel table if necessary. Dynamic array results cannot spill from a formula entered inside an Excel table. If the XLOOKUP formula is in a table column and is supposed to return multiple cells, place it in an ordinary worksheet range outside the table, leaving room for the output. Alternatively, change the formula to return just one value per row if that is what the table is intended to hold. Table formatting itself is not the problem; the limitation is the table’s structured rows and dynamic array spilling behavior.
- Check for merged cells and unusual spill shapes. A merged cell anywhere in the intended output area can prevent a spill. Unmerge it if it is safe to do so, or put the formula in a different location. Also confirm that the return range has the shape you intend: a horizontal return range produces a horizontal result, while a vertical range produces a vertical one. XLOOKUP’s lookup array and return array need compatible dimensions. If the formula should return one item, use a single return column or row rather than trying to force the spill to fit.
- Recalculate and confirm the result. After clearing a blocker or moving the formula, press Enter in the formula cell if needed, then check that each returned value occupies the expected cell. If you made changes to calculation settings or the workbook is in manual calculation mode, use Formulas > Calculate Now on the Windows ribbon. Verify that the returned value belongs to the intended record; a spill error fix should not change which item XLOOKUP matches.
Common Mistakes
Deleting cells without checking their contents: an apparently empty cell may contain a formula, a space, or information someone needs. Inspect the formula bar and preserve valuable content before clearing a blocker. Use a new output area when there is any doubt.
Assuming every XLOOKUP should return only one cell: XLOOKUP can return a whole row or column from its return array. A formula that references multiple return columns may be working as designed; it simply needs space for the complete result. Narrow the return array only if one value is all you want.
Putting a multi-cell result in a table: moving the formula to another cell inside the same table will not make a dynamic array spill. Put it in a normal range, or redesign the formula to produce a single value in each table row.
Wrapping the formula in IFERROR to hide the message: suppressing an error does not create space for the result. It can also conceal a separate problem, such as a misspelled range or an unavailable match. Fix the spill cause first; use an error-handling formula only when you have deliberately decided how errors should be displayed.
Clearing only the first visible obstruction: a spill range can be blocked by more than one cell. After clearing the first blocker, inspect the full dashed outline and repeat the check if #SPILL! remains.
Tips
- Keep a dedicated output area for formulas that return multiple columns. This reduces the chance that new notes or data will later block the result.
- Use a single-column return range when the report needs one field, such as a department or price. A smaller output is easier to place and check.
- Prefer bounded ranges that match the data you need, such as A2:A500, instead of referencing entire columns unnecessarily. This makes the formula’s scope easier to understand and can improve workbook responsiveness.
- When working with a table, use XLOOKUP in a calculated column for one value per row, but put any intended multi-column spill outside the table. Keep the table’s row-by-row design separate from a dynamic array report.
- Before clearing or moving data, save a copy if the workbook is important or shared. This gives you a way to restore the original layout if you remove a value that turns out to be needed.
Troubleshooting
The error message says the spill range is not blank, but the cells look empty. Select the obstructing cells and inspect the formula bar. A space, an invisible-looking character, or a formula returning an empty string still occupies a cell. Clear the contents only after confirming they can be removed. Also check whether the formula’s dashed outline extends farther than expected.
Select Obstructing Cells is unavailable or does not reveal the cause. Select the formula cell and examine the area in the direction the result would expand. Look for merged cells, existing formulas, and values, including content in hidden rows or columns. If the formula is inside a table, move it outside the table to test whether the table restriction is responsible.
The spill range is blocked by important data. Do not overwrite that data. Cut the XLOOKUP formula and paste it into an empty area with enough room, or edit the return array so it produces only the required field. Recheck any references after moving the formula, especially references that were relative and may have shifted.
There is no #SPILL! error, but the formula returns the wrong result or #N/A. That is a different issue. Confirm that the lookup value appears in the lookup array, that the lookup and return arrays line up, and that the formula is returning the intended column. #SPILL! is about placing an array result; it does not by itself mean that XLOOKUP found the wrong match. If you see another error, troubleshoot that specific message instead of clearing cells at random.
The formula still shows #SPILL! after clearing cells. Check for another obstruction elsewhere in the outlined output area, then confirm the formula is not in a table and that no merged cell intersects the output. If the intended range is unusually large, review whether the lookup value or return array is itself an array and is causing more rows or columns to be returned than expected. Reduce the range or move the formula to a clear worksheet area.
Frequently Asked Questions
Why does XLOOKUP show #SPILL! instead of one answer?
XLOOKUP may be returning multiple cells because its return array includes multiple columns or rows, or because the lookup value is an array. Excel displays #SPILL! when it cannot place that complete result in the worksheet.
Can blank-looking cells cause an XLOOKUP spill error?
Yes. A cell containing a space, a formula, or other content is not empty for spill purposes, even if it appears blank. Select the obstruction and inspect its contents before clearing it.
Can XLOOKUP spill inside an Excel table?
A dynamic array formula cannot spill from within an Excel table. Put a formula that returns multiple cells in a normal worksheet range outside the table, or adjust it to return one value per table row.
Will formatting or hidden cells cause #SPILL!?
Formatting by itself generally does not block a spill. Cell contents, merged cells, and table placement are more common causes. Hidden rows or columns can make an obstruction less obvious, so inspect the full intended spill range.
How do I make XLOOKUP return just one value?
Set the return_array argument to the single column or row containing the field you need. For example, use a price column as the return array rather than a range that includes both price and stock.
Does XLOOKUP #SPILL! mean the lookup value was not found?
No. #SPILL! means Excel cannot display an array result in the required cells. A missing lookup normally returns #N/A unless the formula includes an if_not_found value such as “Not found”.
Related
- Excel Trust Center: How to Open and Configure Settings
- How to Enable Undo in Excel and Restore the Undo Button
- Track Changes in Excel Online: View and Review Edits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Dropdown List in Excel: Create, Edit, and Fix OneIn Excel for Microsoft 365 on Windows, select the cells for the dropdown, choose Data > Data Validation,…
- Excel Not Letting You Filter? Fix the Common CausesIf Excel is not letting you filter, first select a cell in the data and choose Data >…
- Excel UPPER Function Not Working? Fix Common CausesIf Excel’s UPPER function is not working, first check that the formula is entered as =UPPER(A2), with the…
- Columns Not Showing in Excel? Fix Hidden or Missing ColumnsIf columns are not showing in Excel, select the column headers on both sides of the gap, right-click,…
- Excel SUBSTITUTE Not Working? Common FixesIf Excel’s SUBSTITUTE function is not working, the usual causes are text that does not match exactly, a…
- Excel Links Broken? Fix External References and LinksUsually, yes: broken Excel links can often be repaired by reconnecting the workbook to its source file or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.