Forum Discussion

ChidiV's avatar
ChidiV
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's avatar
    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")) 

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 29, 2021
Spoiler
 

ChidiV 

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

 

 

 

  • ChidiV's avatar
    ChidiV
    Copper Contributor
    Oct 29, 2021
    Let me try these
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 29, 2021

      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.

Resources