Oct 28 2021 11:51 PM
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
Oct 29 2021 12:20 AM - edited Oct 29 2021 12:24 AM
@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"))
Oct 29 2021 01:12 AM
Oct 29 2021 01:18 AM
@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"))
Oct 29 2021 01:26 AM
Oct 29 2021 01:30 AM
@ChidiV Sorry, but I can't judge what is "wrong" if you don't share the file, or at least part of it, after removing any private or confidential information.
Oct 29 2021 01:43 AM
Solution@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"))
Oct 29 2021 01:57 AM - edited Oct 29 2021 01:58 AM
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")
Oct 29 2021 02:20 AM - edited Oct 29 2021 02:38 AM
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.
Jan 24 2023 02:22 PM
Jan 24 2023 04:01 PM - edited Jan 24 2023 04:11 PM
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.
Oct 29 2021 01:43 AM
Solution@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"))