How to Fix XLOOKUP Spill Error Caused by MAP Formula in Excel
Question details
The user is trying to execute an XLOOKUP formula but encounters a #SPILL! error because a previously entered MAP formula is obstructing the spill range.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Writing an XLOOKUP array formula that needs to spill results into adjacent empty cells.
- Observed behavior
- The valid XLOOKUP formula returns a #SPILL! error instead of the expected results because other data or an unintended MAP formula occupies the required spill area.
Click on the cell displaying the #SPILL! error. Excel will temporarily highlight the intended spill range with a dashed blue border, allowing you to easily spot the cell (such as B8) that is blocking the formula.
Clear the Blocking Formula and Apply Direct XLOOKUP
Remove the unintended MAP formula blocking the spill range and use a standard dynamic array XLOOKUP to retrieve your data.
A #SPILL! error occurs when a dynamic array formula needs to output multiple values, but one or more cells in its output path are not empty. In this case, an unnecessary MAP formula is occupying the space.
Select the cell containing your XLOOKUP formula and look for the obstructed range. Identify the cell (for example, B8) containing the unintended MAP formula or stray data.
Click on the blocking cell and press the 'Delete' key on your keyboard to clear its contents. The #SPILL! error may resolve immediately if the original formula was correct.
If you need to rewrite the formula, use a direct array approach without MAP. Type: =XLOOKUP(B4#&A4#, B4#&D4#, C4#) into the target cell.
To prevent #N/A errors for missing values, add the if_not_found argument. Modify the formula to: =XLOOKUP(B4#&A4#, B4#&D4#, C4#, "not found") and press Enter.
Easily Manage Dynamic Arrays and XLOOKUP with WPS Office
WPS Office provides robust support for modern spreadsheet functions, including XLOOKUP and dynamic arrays, ensuring your complex data calculations are smooth and error-free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your lookup data.
- 2. Identify the spill error: Click the cell showing the #SPILL! error to trace the blocking data in the adjacent cells.
- 3. Clear the range: Highlight the obstructing cells and press Delete to clear the path for your dynamic array.
- 4. Apply XLOOKUP: Type your XLOOKUP formula, such as =XLOOKUP(B4#&A4#, B4#&D4#, C4#, "not found"), and press Enter to watch your data spill perfectly.
Frequently Asked Questions
What does the #SPILL! error mean in Excel?
The #SPILL! error indicates that a formula is trying to output an array of multiple values into adjacent cells, but one or more of those target cells already contain data, spaces, or other formulas.
How do I find which cell is causing the #SPILL! error?
Select the cell with the #SPILL! error. A warning icon will appear next to it. Click the icon and select 'Select Obstructing Cells' to immediately jump to the cell that is blocking your formula's output.
Can I use MAP and XLOOKUP together?
Yes, you can nest XLOOKUP inside a MAP function if you need to apply a custom LAMBDA calculation to each element of an array. However, for standard array lookups, a direct XLOOKUP using dynamic arrays (like B4#) is simpler and more efficient.
How do I prevent #N/A errors when my XLOOKUP doesn't find a match?
You can use the built-in 'if_not_found' argument in the XLOOKUP function. Simply add your desired fallback text or a blank string at the end of the formula, for example: =XLOOKUP(lookup_value, lookup_array, return_array, "Not Found").
