SOLVED

SPILL Error on IF function

Copper Contributor

Hi All, 

I'm having trouble locating the error in my formula. I have a data sheet '012021' where column E is a list of locations (some duplicated) and f-g a various number values. The duplicate cities have erroneous data attached, so I only need the rows that have the largest value in column f. 

Cleaning up the data and removing duplicates isn't an option as the file is just too large.

Finding the value of the matching city in column f is easy D3 is Philadelphia, PA for this example: 
=MAX(IF('012021'!E:E=D3,'012021'!F:F) 

Yet when I try to find G, I get a spill error: 
=IF('012021'!E:E=C6,IF('012021'!F:F=D6),'012021'G:G))

I even tried changing the return value to a single string and I'm still getting a SPILL error. 
=IF('012021'!E:E=C6,IF('012021'!F:F=D6),"hey it finally worked"))


What am I missing?

4 Replies

@dasoidas 

If you enter that formula in any cell in first row, it returns an array of 1048576 rows with result.

If in any other row it returns #SPILL! error since there is not enough space to place more than one million of values.

Not sure what you try to return exactly, in any case use ranges, tables or dynamic ranges, not entire column in calculations.

By the way, more correct syntax

=IF('012021'!E:E=C6,IF('012021'!F:F=D6,'012021'!G:G,))
yup syntax was a typo, the sheet names are actually an indirect function to use a cell value to direct to the correct sheet, and when editing it to post here I missed removing a parentheses.

So I can't do ranges dynamic or otherwise due to the multiple sheets with varying sizes. (and a number of other reasons). I could make a macro that edits the formulas each time we refer to a different sheet, but thats extra work to create a workaround, rather than resolving the formula error.


I understand it's returning too many results, the question is why. There is only 1 row where D6 and C6 match. In fact there's only 1 row where d6 matches.

Also in the last formula I provided, I"m returning a singular string and not any array, yet there's still a SPILL error. If the first if is true, it returns the second if, which, if true returns ""hey it finally worked". The if's should either be true or false, and return the true value. But even when the true value is a string, its returning an array.

@dasoidas 

Latest formula also returns 1 million values array in each element of it we have text if condition is met for related row in columns E and F, otherwise FALSE.

=IF( column, something) returns as many elements as you have them in entire column.

best response confirmed by Grahmfs13 (Microsoft)
Solution
=IF( column, something) returns as many elements as you have them in entire column.
Ah ok that's the part I was missing. I didn't realize IF functions iterate and return through each instance.

Thank you Sergei!

For anyone that runs into this I resolved using XLOOKUP (which I had originally tried but other data issues made that not an option. Luckily those data issues aren't any issue anymore.
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution
=IF( column, something) returns as many elements as you have them in entire column.
Ah ok that's the part I was missing. I didn't realize IF functions iterate and return through each instance.

Thank you Sergei!

For anyone that runs into this I resolved using XLOOKUP (which I had originally tried but other data issues made that not an option. Luckily those data issues aren't any issue anymore.

View solution in original post