Forum Discussion
#SPILL
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"))
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"))
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.
