The tutorial explains what may cause a #SPILL error in an INDEX MATCH, VLOOKUP, SUMIF and COUNTIF formula and how you can efficiently resolve it.
It is sad enough when a brand-new feature refuses to work in your Excel. But even more frustrating is when a good old thing stops working all of a sudden, and you are receiving an error for a formula that worked perfectly for years.
The below examples show how to fix a few common formulas that got broken because implicit intersection is no longer invisibly performed in Excel. If you have never heard this term before, I encourage you to carefully read the #SPILL error tutorial to understand what is happening behind the scenes.
#SPILL error with Excel VLOOKUP formula
Here is a standard VLOOKUP formula that works fine in pre-dynamic Excel (2019 and earlier), and triggers in a #SPILL error in Excel 365:
=VLOOKUP(A:A, D:E, 2, FALSE)
As we can reasonably assume, the problem is in the first argument (the red reference above) that forces the VLOOKUP function to look up all the values in column A, which is over a million cells (the exact number is 1,048,576)! In the past, that was not a problem - Excel could only look up one value at a time, so it discarded all but one value in the same row as the formula. This behavior is called implied or implicit intersection.
With the introduction of dynamic arrays, all Excel functions got the ability to process and output multiple values, even those that were not initially designed to work with arrays! So, each time VLOOKUP receives an array of lookup values, it tries to handle them all. In case there isn't enough space to output all the results, you see a #SPILL error.
To resolve an Excel VLOOKUP spill error, you can use one of the following methods.
Look up range rather than column
As we only have 3 lookup values, we limit the lookup_value argument to three cells:
=VLOOKUP(A3:A5, D:E, 2, FALSE)
The formula needs to be entered just in one cell and it will fill as many cells as needed automatically. The result is a spill range like this one:
Look up a single value
Write a formula for the first lookup value and copy it down through as many cells as needed:
=VLOOKUP(A3, D:E, 2, FALSE)
It is my preferred option as it is simplest to implement and works flawlessly in all Excel versions, from within normal ranges and tables.
Enforce implicit intersection
To limit an array to one lookup value, place the intersection operator @ before the column reference:
=VLOOKUP(@A:A, D:E, 2, FALSE)
As with the previous example, you enter the formula in one cell and drag it down the column.
Whichever solution you choose, a #SPILL error should be gone and your VLOOKUP formula starts working normally in Excel 365.
#SPILL error with Excel INDEX MATCH formula
In case you are using the combination of INDEX and MATCH functions to pull matches, a #SPILL error can arise for the same reason - there is insufficient white space for the spilled array.
For example, here's the formula that flawlessly returns sales numbers in Excel 2019 and earlier versions, but refuses to work in Excel 365:
=INDEX(E:E, MATCH(A:A, D:E, 0))
The remedy is already known - reduce the number of lookup values by applying one of the following techniques.
- Look up a range, not a column:
=INDEX(E:E, MATCH(A3:A5, D:D, 0))
- Look up a single value:
=INDEX(E:E, MATCH(A3, D:D, 0))
- Enable implicit intersection - add the @ character before a column reference, so that Excel processes only one value:
=INDEX(E:E, MATCH(@A:A, D:D, 0))
The result of the 1st formula is a dynamic spilled array, which is a great thing that saves you the trouble of copying the formula to other cells. The limitation is that dynamic arrays only work in a range, not a table.
The 2nd and 3rd formulas return a single value, which a table can also accept. If your data is organized as a regular range, drag the formula down to copy it to the below cells. In a table, the formula will propagate automatically. In the latter case, you can also use a structured reference notation referring to the column headers:
=INDEX(E:E, MATCH([@[Seller ]], D:D, 0))
The below screenshots demonstrate the 3rd formula in action:
#SPILL error with Excel SUMIF and COUNTIF formula
Spill range is too big
A very typical cause is supplying a whole column for criteria. Yep, that used to work in older Excel versions, but not anymore, since the new spilling feature produces nearly 1.05 million results and there is not enough space to accommodate them all!
For this example, let's try to find a total of sales made by three vendors (A3:A5). In Excel 2019 and lower versions, you could successfully use the below syntax. In Excel 365, you will be getting a #SPILL error:
=SUMIF(D:D, A:A, E:E)
If you followed the previous examples closely, you know that the error can be resolved in three different ways:
- Use a range for your criteria, not a whole column:
=SUMIF(D:D, A3:A5, E:E)
- Define a single cell for the criteria and copy the formula down:
=SUMIF(D:D, A3, E:E)
- Include the implicit intersection operator (@) to limit the column reference to one cell:
=SUMIF(D:D, @A:A, E:E)
In a similar fashion, you can get a count of sales for each person by using the COUNTIF function:
=COUNTIF (D:D, A3)
=COUNTIF (D:D, @A:A)
Please remember that the 1st formula spills automatically into the below rows and can only be used within a range, not a table.
The 2nd and 3rd formulas return a single value, so you enter them in the first cell and then copy down the column as usual.
Criteria expressed incorrectly
The SUMIF and COUNTIF criteria are also a common source of problems. Sometimes, people overthink it and write the criteria as D3="carter" or D3:D11="carter" or D:D="carter". All three expressions are wrong and cause a formula to produce zero or a #SPILL error!
The correct way is either a range/cell reference like in the above examples, or text enclosed in quotation marks:
=SUMIF(D:D, "carter", E:E)
To learn more about what is acceptable in criteria and what is not, the following guidelines might be helpful: SUMIF criteria syntax.
Wrong order of arguments
In singular and plural versions of the SUMIF function, the order of arguments is different:
- With SUMIF, you first define range, then criteria, then sum_range (optional).
- In case of SUMIFS, the first argument should be the sum_range, and then range/criteria pairs.
If you muddle things up, a #SPILL error occurs.
In our example, criteria_range is D:D and sum_range is E:E. If you put one in place of the other, the formula will throw a #SPILL error again:
=SUMIFS(D:D, A3:A5, E:E)
Arrange the arguments in the correct order, and SUMIF will give you the desired result:
=SUMIFS(E:E, D:D, A3:A5)
That's how to resolve a #SPILL error with Excel INDEX MATCH, VLOOKUP, SUMIF and other functions. I thank you for reading and hope to see you on our blog next week!