Forum Discussion
Solved
SPILL ERROR CAUSING MORE WORK NOT REDUCING IT _ MAKE IT OPTIONAL!
OK, so I admit I am not a power user and frankly have no real use for the SPILL thing. All it does is cause me frustration and anger at having doing more where before it wasn't required. I want to use this formula to look at a row of unique numbers and see if one from another table is there. The formula I am using used to work no problem. But now I get Spill error and I have no way to figure out how to correct it. The instructions/solutions I've seen don't match what is showing up on my worksheet.
Here's the simple forumula:
=IF(A12=(A3:A8),"Y","N")
A3-A5 is the range I want it to look at and consists of unique numbers, no repeats.
A12 is the number I want to match to see if any of the cells A3:A8 contains it.
When I highlight the formula there is a dotted blue line around the formula cell and the 6 cells below. That I assume is the spill area. But how do I build this table so that it looks up more numbers in the column to match? So far everything anyone has suggested that I've found doesn't get rid of the #Spill error. What exactly is it trying to do and why would you screw up such a basic spread sheet functions like this for something many of us will never use?
In my opinion you screwed up your formula.
The part A12=... requires a single cell and not an array of cells. The correct form would have been:
=IF(A$12=A3,"Y","N")The reason why it still worked in old Excel is "implicit intersection".
In new Excel you have to explicit use the implicit intersection operator.
=@IF(A12=(A3:A8),"Y","N")Or just let the formula spill.
In my opinion you screwed up your formula.
The part A12=... requires a single cell and not an array of cells. The correct form would have been:
=IF(A$12=A3,"Y","N")The reason why it still worked in old Excel is "implicit intersection".
In new Excel you have to explicit use the implicit intersection operator.
=@IF(A12=(A3:A8),"Y","N")Or just let the formula spill.
Detlef_Lewin thank you so much for this. Using the @ worked in my SumIf and resolved the error I encountered. For those of us that didn't understand the change in Excel and what it was doing, your brief explanation below helps to clarify and will help with other formulas.
