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"))
- 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"))
- Tried it, at least there'
s no error now. But my results are wrong even with the correct logic.
