Formula Errors

How to Fix Excel #SPILL! Errors with INDEX and MATCH Across Workbooks

Steven Parker Oct 22, 2026 869 views

Question details

The user needs to resolve a #SPILL! error triggered when using INDEX and MATCH functions to look up data in an external workbook.

How to Fix Excel #SPILL! Errors with INDEX and MATCH Across Workbooks

Product: Excel
Device & OS: not provided
Scenario: Pulling data from an external workbook using lookup functions like INDEX and MATCH, which unexpectedly returns an array instead of a single value.
Observed behavior: The formula returns a #SPILL! error because the dynamic array attempts to return multiple values into a range that is blocked or unavailable.

Before you start

Ensure the target cells directly below your formula are completely empty, and unmerge any merged cells in the anticipated spill range.

Solution 1

Clear the Obstructed Spill Range

The #SPILL! error often occurs when cells adjacent to your formula already contain data, spaces, or hidden characters.

In modern versions of Excel, formulas that return multiple values will automatically spill into neighboring cells. If anything blocks that path, the calculation fails.

Locate the obstruction

Click the cell displaying the #SPILL! error. A dashed blue border will outline the intended spill range.

Highlight the range

Select all the cells within that dashed border to identify any existing data.

Clear the cells

Press the Delete key or right-click and choose 'Clear Contents' to remove any data, spaces, or formatting. The formula will automatically populate once the range is clear.

Solution 2Recommended

Adjust the INDEX and MATCH Formula

Ensure your MATCH function is looking up a single value rather than an entire array to prevent unnecessary spilling.

Solution 3

Upgrade to the XLOOKUP Function

If you are using Microsoft 365 or Excel 2021, replacing INDEX and MATCH with XLOOKUP simplifies cross-workbook lookups.

Solve Formula Errors Easily

Easily Manage Cross-Workbook Formulas in WPS Office

WPS Office fully supports dynamic arrays, INDEX, MATCH, and XLOOKUP. You can seamlessly pull data across workbooks without worrying about complex compatibility issues.

1. Open Your Workbooks: Launch WPS Spreadsheets and open both your destination workbook and the source workbook.
2. Enter the Formula: Select your target cell and input your INDEX/MATCH or XLOOKUP formula, referencing the external workbook ranges as normal.
3. Manage Dynamic Arrays: If you encounter a #SPILL! error, simply highlight the empty cells below your formula, right-click, and select 'Clear Contents' to allow the array to expand naturally.

100% compatible with Microsoft Excel formulas (.xlsx)Supports advanced lookup functions including XLOOKUP and dynamic arraysLightweight application that handles large datasets smoothlyFree to download and use for essential spreadsheet tasks

Frequently Asked Questions

What exactly does a #SPILL! error mean in Excel?

A #SPILL! error occurs when a dynamic array formula tries to output multiple results, but the required cells (the spill range) are blocked by existing data, spaces, or merged cells.

Why does the MATCH function cause a #SPILL! error?

If the lookup_value argument in your MATCH function is an array or an entire column instead of a single cell reference, it will return an array of results. If there is no room for this array to populate downward, it spills.

Can I fix a #SPILL! error by turning off dynamic arrays?

Dynamic arrays are a built-in feature in modern spreadsheet software (like Microsoft 365, Excel 2021, and recent WPS versions) and cannot be turned off. You must clear the blocked range or use implicit intersection (adding an @ symbol before the function) to force a single result.

Does WPS Office support XLOOKUP across different files?

Yes, WPS Spreadsheets fully supports the XLOOKUP function, including cross-workbook references, making it an excellent free alternative for complex data lookups.

Excel & Data Problems