Forum Discussion
Solved
SPILL Error on IF function
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 ci...
- =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.
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.
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.
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.
- =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.