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

     

mdeanda205's avatar
mdeanda205
Copper Contributor
Jan 24, 2023
Hi Riny, I had the same type of problem. what is a dynamic array and what does the @ mean? Riny_van_Eekelen
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 24, 2023

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.

Resources