SOLVED

#SPILL

Copper Contributor

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

 

 

 

 

14 Replies

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

=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.

@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.

I'll share the file cause im at frustration level
best response confirmed by allyreckerman (Microsoft)
Solution

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

 

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

 

 

 

Let me try these

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.

COUNTIF works well :thumbs_up:

@ChidiV Great! Should have thought about that from the start :)

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.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

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

 

View solution in original post