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:
Notes:
- If you are getting a #SPILL error anyway, make sure the spill range is empty, so nothing gets in the way.
- This solution does not work within an Excel table because tables do not support dynamic arrays.
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
When a SUMIF, COUNTIF, SUMIFS or COUNTIFS formula returns a #SPILL error, it might be caused by many different factors. The most often ones are discussed below.
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:A5)=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!
You may also be interested in:
- #SPILL error in Excel
- Excel dynamic arrays, functions and formulas
- How to write recursive LAMBDA function with examples
- How to stop text from overflowing into the next columns in Excel
FAQs
How do I fix spill error in Excel Sumif? ›
This means that one or more merged cells are obstructing the spill range. To fix this error, unmerge the merged cells or delete them. If you cannot visually locate them, click on the Select Obstructing cells option to select them.
How do I fix spill error in Excel INDEX match? ›- 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))
To remove a particular spill range, delete a formula in the first cell. To prevent a formula from spilling into multiple cells, use the @ operator which reduces multiple values to a single value. In terms of Excel, this is called implicit intersection.
What is the spill error in sum product? ›SPILL error is caused when a formula with multiple results cannot display its output array as those cells already contain some data. A simple solution to this problem is to clear contents of the cells in the spill range.
How do I get rid of spill error in Vlookup? ›You can select the Error floatie, and choose the Select Obstructing Cells option to immediately go the obstructing cell(s). You can then clear the error by either deleting, or moving the obstructing cell's entry. As soon as the obstruction is cleared, the array formula will spill as intended.
Why does VLOOKUP give me a spill error? ›This means that if an entire column is given as the lookup_value argument, Excel will attempt to lookup all 1,048,576 values in the column. Once it's done, it will attempt to spill them to the grid, and will very likely hit the end of the grid resulting in a #SPILL! error.
Why is VLOOKUP not picking up values? ›Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.
What is the Sumifs function in Excel? ›The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value. Play.
How do I allow a spill in Excel? ›The formula is =B2:B10-F2:E10 or =B2:B10F2#. Excel uses the pound sign (#) to reference a spilled range, and that's what will appear if you build the formula by selecting the cells F2:F10 , as shown in the screenshots below. For Excel 365, formulas that return a set of values (or an array) are able to spill.
What causes a spill formula in Excel? ›Spill means that a formula has resulted in multiple values, and those values have been placed in the neighboring cells. For example, =SORT(D2:D11,1,-1), which sorts an array in descending order, would return a corresponding array that's 10 rows tall.
How do you fix spill range in table error? ›
Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (click Table Design > Tools > Convert to range).
What is the difference between Sumif and Sumifs? ›The only difference between Excel SUMIFS & SUMIF functions is that SUMIFs can check for multiple criteria at once, while SUMIF can check for one criterion at a time. The SUMIF formula returns the sum of cells based on one criterion (a result that matches one condition).
Can I have Excel create new rows instead of throwing a spill error? ›However, a SPILL error is thrown if there are non-blank cells that would be overwritten. Is it possible to have excel just create new rows to avoid the SPILL error? I would ideally just like to shift all the cells down. Short answer: 'No' Formulas cannot do that.
How do I know if VLOOKUP returns error? ›If the VLOOKUP function cannot find a specified value, it throws an #N/A error. To catch that error and replace it with your own text, embed a Vlookup formula in the logical test of the IF function, like this: IF(ISNA(VLOOKUP(…)), "Not found", VLOOKUP(…))
Is there an alternative to VLOOKUP? ›The superior alternative to VLOOKUP is INDEX MATCH. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH.
What is the difference between VLOOKUP and index match? ›The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from.
What format do cells need to be in for VLOOKUP? ›The format of the VLOOKUP function is: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup). The lookup_value is the user input. This is the value that the function uses to search on.
Why is SUMIF returning 0? ›SUMIFS returns the sum of cells that meet the given criteria. If no cells meet the criteria then the sum will be zero.
Why is sumifs not working with multiple criteria? ›To sum cells that match multiple criteria, you normally use the SUMIFS function. The problem is that, just like its single-criterion counterpart, SUMIFS doesn't support a multi-column sum range. To overcome this, we write a few SUMIFS, one per each column in the sum range: SUM(SUMIFS(…), SUMIFS(…), SUMIFS(…))
How does index and match work in Excel? ›=INDEX() returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.
What is the filter function in Excel spill? ›
The FILTER function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER.
What is the spill range in Xlookup? ›That's called the spill range. If you see a spill error, then the range needed to fulfill the function isn't available. What this means is that you can use one function to return multiple columns (or rows) of resulting values. XLOOKUP() returns data in a table or range by row.
How does index match work? ›INDEX MATCH is made up of two functions. The MATCH function is used to figure out where the lookup value is located in a range of cells. The INDEX function then returns the value or cell reference from within a table or range. The MATCH and INDEX functions are combined to do the job that VLOOKUP cannot do.
What is the formula for Vlookup? ›In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
Why am I getting #value in Excel? ›Often #VALUE! occurs because your formula refers to other cells that contain spaces, or even trickier: hidden spaces. These spaces can make a cell look blank, when in fact they are not blank.
What is the past tense of spill? ›Word forms: plural, 3rd person singular present tense spills , present participle spilling , past tense, past participle spilled , past tense, past participle spilt language note: American English uses the form spilled as the past tense and past participle.
How do you reset a table range? ›Select the table, then select Table Design > Resize Table. Adjust the range of cells the table contains as needed, then select OK.
How do you clear a table range? ›Select all the cells in the table, click Clear and pick Clear All. Tip: You can also select the table and press Delete. If you want to keep the data without the table format, you won't be able to do that in Excel for the web. Learn more about using the Excel desktop application to convert a table to a data range.
Can you use Sumif and VLOOKUP together? ›You can use VLOOKUP and SUMIF (or SUMIFS for multiple criteria) together in Excel for various purposes—for example: VLOOKUP within SUMIF, when you need to sum values based on conditions, but you also have to lookup from another table to get the correct criteria value.
What is the best alternative to Sumifs? ›With SUMPRODUCT
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
How do you use sum and Sumifs together? ›
Syntax. =SUMIF(range1, criteria1, [sum_range1]) + SUMIF(range2, criteria2, [sum_range2])+... This formula works like an OR logical formula, which sums values for every criteria that is satisfied.
What is spill error in Excel lookup? ›This means that if an entire column is given as the lookup_value argument, Excel will attempt to lookup all 1,048,576 values in the column. Once it's done, it will attempt to spill them to the grid, and will very likely hit the end of the grid resulting in a #SPILL! error.
Why is Sumif returning an array? ›A SUMIF or SUMIFS formula most certainly can take an array as a criteria argument. It will then return an array, which may need to be SUM 'd depending on what you want to do with the results.
How do you do a Sumif with multiple criteria? ›The syntax for SUMIFS is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The first three arguments are required, with additional arguments to be added depending on how many criteria are to be included. The first required argument is sum_range, which are the actual cells to sum.
Why am I getting an error on VLOOKUP? ›The most common cause of the #N/A error is with XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, or MATCH functions if a formula can't find a referenced value. For example, your lookup value doesn't exist in the source data. In this case there is no “Banana” listed in the lookup table, so VLOOKUP returns a #N/A error.
What causes a formula to spill in Excel? ›Spill means that a formula has resulted in multiple values, and those values have been placed in the neighboring cells. For example, =SORT(D2:D11,1,-1), which sorts an array in descending order, would return a corresponding array that's 10 rows tall.
Why is my Sumif formula returning wrong value? ›The SUMIF/SUMIFS functions returns incorrect results when you try to match strings longer than 255 characters.
Does Sumifs work with arrays? ›Like in the previous example, the SUMIF function returns an array of numbers, representing the sums for each individual condition. SUMPRODUCT adds these numbers together and outputs a final total.
Can you do a Sumif across multiple columns? ›You have to use the SUMIFS function in Excel to sum values with multiple criteria, as the SUMIF function can handle only one criterion at a time. That is SUMIF multiple columns usage is not allowed in Excel.