Forum Discussion

Copper Contributor

Oct 28, 2021

Solved

#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

excel

Riny_van_Eekelen
Oct 29, 2021
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"))

Riny_van_Eekelen

Platinum Contributor

Oct 29, 2021

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"))

ChidiV

Copper Contributor

Oct 29, 2021

=IF([@[Article Base Code]] ="RD*","DAIRY",IF([@[Article Base Code]] ="CS*","PIGS"))

It doesnt seem to work either.

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