Forum Discussion
#SPILL
Hi Everyone,
I have this formula
=IF([Article Base Code] ="RD*","DAIRY",IF([Article Base Code] ="CS*","PIGS"))
I am getting #SPILL ERROR
Please help
ChidiV Wait!! YOu can't use the wild card match that way in an IF function.
Try it this way:
=IF(COUNTIF([@Article Base Code],"RD*"),"DAIRY",IF(COUNTIF([@Article Base Code],"CS*"),"PIGS"))
14 Replies
Assuming Riny_van_Eekelen has diagnosed the problem correctly and you attempting to propagate a spilt array within a Table (maybe by accident) you have two courses of action open to you.
One is to stay within the table but restrict the Base Code reference to a single value
=SWITCH([@[Article Base Code]],"RD*","Dairy","CS*","Pigs","Blank")The other is to move the formula away from the table and allow it to generate a spilt array
= SWITCH(Table1[Article Base Code], "RD*","Dairy", "CS*","Pigs", "Blank")- Let me try these
A further thought. Are your codes exactly "RD*" or do they include other characters, e.g. RD123?
If the latter, you will need to use the LEFT function or even SEARCH.
ChidiV The formula is trying to display a dynamic array. You can't use such a dynamic array formula inside a structured table. If the formula is not inside such a table, some of the cells where the array should be spilt into are not empty. Move them out of the way or move your formula.
Edit: If used inside a dynamic table, try this one:
=IF(@[Article Base Code] ="RD*","DAIRY",IF(@[Article Base Code] ="CS*","PIGS"))
- Hi Riny, I had the same type of problem. what is a dynamic array and what does the @ mean? Riny_van_Eekelen
The @ restricts a reference to a column of the table so that it only returns a single value that is found at the intersection of the record containing the formula and the specified field. The purpose is to ensure the formula returns a scalar result.
Dynamic arrays were introduced to Excel 365 in September 2018 and allow a formula that will evaluate to an array to be placed within a cell. The array will display using as many adjacent empty cells as it needs (hence the 'dynamic'). Prior to that, one would write a formula in a cell but then replicate the formula to derive other values.
- =IF([@[Article Base Code]] ="RD*","DAIRY",IF([@[Article Base Code]] ="CS*","PIGS"))
It doesnt seem to work either.ChidiV Sorry! I put the @ sign in the wrong place. It should by inside the square brackets. Try this:
=IF([@Article Base Code] ="RD*","DAIRY",IF([@Article Base Code] ="CS*","PIGS"))
