#SPILL! error with Excel VLOOKUP, INDEX MATCH and SUMIF resolved

This tutorial explains how to troubleshoot the #SPILL! error in Excel formulas like INDEX MATCH, VLOOKUP, SUMIF, and COUNTIF. The error often arises because implicit intersection—Excel's previous behavior of handling single values from a larger range—no longer automatically occurs in dynamic array-enabled versions (Excel 365 and later).

Older formulas that worked flawlessly in Excel 2019 and earlier might now produce this error. The core issue is that these functions, when given entire columns as input, attempt to process and output millions of results, exceeding available space.

Resolving the #SPILL! Error

The solutions involve restricting the input range to a manageable size:

1. VLOOKUP:

The problematic formula: =VLOOKUP(A:A, D:E, 2, FALSE)

Solutions:

• Specify a range: =VLOOKUP(A3:A5, D:E, 2, FALSE) (This creates a spilled array; works in ranges, not tables.)
• Single-cell lookup: =VLOOKUP(A3, D:E, 2, FALSE) (Copy down as needed; works in ranges and tables.)
• Force implicit intersection: =VLOOKUP(@A:A, D:E, 2, FALSE) (Enter in one cell and copy down; works in ranges and tables.)

2. INDEX MATCH:

The problematic formula: =INDEX(E:E, MATCH(A:A, D:E, 0))

Solutions: Similar to VLOOKUP, use a range, single-cell lookup, or the @ operator to limit the input:

• Range lookup: =INDEX(E:E, MATCH(A3:A5, D:D, 0)) (Spilled array; ranges only.)
• Single-cell lookup: =INDEX(E:E, MATCH(A3, D:D, 0)) (Copy down as needed; ranges and tables.)
• Implicit intersection: =INDEX(E:E, MATCH(@A:A, D:D, 0)) (Enter in one cell and copy down; ranges and tables.) Also usable with structured references in tables: =INDEX(E:E, MATCH([@[Seller]], D:D, 0))

3. SUMIF and COUNTIF:

The problematic formula (example): =SUMIF(D:D, A:A, E:E)

Solutions:

• Range for criteria: =SUMIF(D:D, A3:A5, E:E) (Spilled array; ranges only.)
• Single-cell criteria: =SUMIF(D:D, A3, E:E) (Copy down as needed; ranges and tables.)
• Implicit intersection: =SUMIF(D:D, @A:A, E:E) (Enter in one cell and copy down; ranges and tables.)

Additional Considerations:

• Correct Criteria: Ensure criteria are correctly formatted (e.g., "carter" for text).
• Argument Order: Double-check the argument order in SUMIF and SUMIFS.
• Spill Range: Ensure the spill range is clear of obstructions.

By applying these methods, you can effectively resolve #SPILL! errors and restore the functionality of your Excel formulas. Remember to choose the solution that best fits your data structure (range vs. table) and desired output (spilled array vs. single values).

The above is the detailed content of #SPILL! error with Excel VLOOKUP, INDEX MATCH and SUMIF resolved. For more information, please follow other related articles on the PHP Chinese website!