Formula Errors

Fix Excel #SPILL! Errors with INDEX, INDIRECT, and MAXIFS

Muhammad Talha Oct 8, 2026 870 views

Question details

The user needs to fix formula errors (#SPILL!, #N/A, or zero results) when trying to return a region associated with the largest value using INDEX, INDIRECT, and MAXIFS functions.

Fix Excel #SPILL! Errors with INDEX, INDIRECT, and MAXIFS

Product: Excel
Device & OS: not provided
Scenario: Attempting to find the maximum value across multiple specific regions (like Asia Pacific, Europe, North America) and returning the corresponding region name using complex lookup functions.
Observed behavior: The formula returns zero because using multiple MAXIFS criteria on the same column creates an impossible AND condition, or returns #SPILL!/#N/A due to array conflicts or invalid INDIRECT references.

Before you start

Ensure that the cells below and to the right of your formula are completely empty to prevent #SPILL! errors, and verify that any external workbooks referenced by INDIRECT are currently open.

Solution 1Recommended

Use FILTER and LET with an OR Condition

Replace conflicting MAXIFS criteria with an OR-based FILTER function to prevent zero returns and evaluate multiple regions correctly.

When you specify multiple different criteria for the exact same column using MAXIFS (e.g., region must be 'Europe' AND 'Asia Pacific'), Excel evaluates it as an AND condition. Since a single cell cannot hold two different values simultaneously, it returns zero.

To fix this, use the FILTER function to create an OR condition by adding arrays together with the plus (+) operator, then wrap it in the LET function to calculate the maximum value.

Select the destination cell

Click on the empty cell where you want the highest region value to be displayed.

Build the OR-based filter

Construct a FILTER formula for your target data (e.g., Column M) using an OR condition for the regions. Use the syntax: ((A:A="Asia Pacific") + (A:A="Europe") + (A:A="North America") + (A:A="ROW")).

Wrap with LET and MAX

Use the LET function to assign the filtered array to a variable, then use the MAX function to find the highest value within that specific filtered array.

Return the region with INDEX

Finally, use INDEX and MATCH to look up the calculated maximum value in your filtered data and return the matching region name from Column A.

Understanding the OR Operator: In dynamic array formulas, using the plus sign (+) between logical expressions evaluates them as OR, ensuring that if any of the region criteria are met, the data is included in the filter.

Solution 2

Troubleshoot #N/A Errors in INDIRECT References

Fix #N/A errors by ensuring that INDIRECT functions correctly locate your lookup ranges and labels.

Advanced Spreadsheets

Resolve Complex Formula Errors Seamlessly with WPS Spreadsheet

WPS Office Spreadsheet provides full support for modern dynamic arrays, including functions like LET, FILTER, and MAXIFS. Easily write complex data lookups without compatibility issues or unexpected #SPILL! errors.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing the broken formula.
2. Use Error Checking: Navigate to the 'Formulas' tab and click 'Error Checking' to locate the source of the #SPILL! or #N/A error.
3. Rewrite the logic: Apply the FILTER and OR condition (+) logic, which is fully supported and optimized in WPS Spreadsheet.
4. Execute the dynamic array: Press Enter to see the dynamic array spill correctly into adjacent cells without returning zero.

100% compatible with Microsoft Excel formulas and dynamic arrays.Clear error tracing to easily identify #SPILL! or #N/A root causes.Familiar user interface makes rewriting complex formulas straightforward.Completely free to use for advanced spreadsheet calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why does MAXIFS return 0 when I select multiple criteria for the same column?

MAXIFS processes multiple criteria as an AND condition. Since a single cell cannot simultaneously contain two different text values (like 'Europe' AND 'Asia Pacific'), it finds no matches and defaults to returning 0. You must use an OR condition via the FILTER function instead.

What causes a #SPILL! error in dynamic array formulas?

A #SPILL! error occurs when a dynamic array formula attempts to output multiple results into adjacent cells, but one or more of those destination cells already contain data, spaces, or hidden formatting. Clearing the blocking cells resolves the error.

Can I use INDIRECT to reference closed workbooks?

No, the INDIRECT function requires the referenced external workbook to be open in the background. If the workbook is closed, the formula cannot evaluate the text string into a valid reference and will immediately return a #REF! or #N/A error.

Excel & Data Problems