SOLVED

#SPILL

%3CLINGO-SUB%20id%3D%22lingo-sub-2899153%22%20slang%3D%22en-US%22%3E%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899153%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20this%20formula%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(%5BArticle%20Base%20Code%5D%20%3D%22RD*%22%2C%22DAIRY%22%2CIF(%5BArticle%20Base%20Code%5D%20%3D%22CS*%22%2C%22PIGS%22))%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20getting%20%23SPILL%20ERROR%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2899153%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899227%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199515%22%20target%3D%22_blank%22%3E%40ChidiV%3C%2FA%3E%26nbsp%3BThe%20formula%20is%20trying%20to%20display%20a%20dynamic%20array.%20You%20can't%20use%20such%20a%20dynamic%20array%20formula%20inside%20a%20structured%20table.%20If%20the%20formula%20is%20not%20inside%20such%20a%20table%2C%20some%20of%20the%20cells%20where%20the%20array%20should%20be%20spilt%20into%20are%20not%20empty.%20Move%20them%20out%20of%20the%20way%20or%20move%20your%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20If%20used%20inside%20a%20dynamic%20table%2C%20try%20this%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(%3CFONT%20color%3D%22%23FF0000%22%3E%40%3C%2FFONT%3E%5BArticle%20Base%20Code%5D%20%3D%22RD*%22%2C%22DAIRY%22%2CIF(%3CFONT%20color%3D%22%23FF0000%22%3E%40%3C%2FFONT%3E%5BArticle%20Base%20Code%5D%20%3D%22CS*%22%2C%22PIGS%22))%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899379%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899379%22%20slang%3D%22en-US%22%3E%3DIF(%5B%40%5BArticle%20Base%20Code%5D%5D%20%3D%22RD*%22%2C%22DAIRY%22%2CIF(%5B%40%5BArticle%20Base%20Code%5D%5D%20%3D%22CS*%22%2C%22PIGS%22))%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20doesnt%20seem%20to%20work%20either.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899388%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199515%22%20target%3D%22_blank%22%3E%40ChidiV%3C%2FA%3E%26nbsp%3BSorry!%20I%20put%20the%26nbsp%3B%40%20sign%20in%20the%20wrong%20place.%20It%20should%20by%20inside%20the%20square%20brackets.%20Try%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(%5B%3C%2FSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E%40%3C%2FFONT%3E%3CSPAN%3EArticle%20Base%20Code%5D%20%3D%22RD*%22%2C%22DAIRY%22%2CIF(%5B%3C%2FSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E%40%3C%2FFONT%3E%3CSPAN%3EArticle%20Base%20Code%5D%20%3D%22CS*%22%2C%22PIGS%22))%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899398%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899398%22%20slang%3D%22en-US%22%3ETried%20it%2C%20at%20least%20there'%3CBR%20%2F%3Es%20no%20error%20now.%20But%20my%20results%20are%20wrong%20even%20with%20the%20correct%20logic.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899406%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199515%22%20target%3D%22_blank%22%3E%40ChidiV%3C%2FA%3E%26nbsp%3BSorry%2C%20but%20I%20can't%20judge%20what%20is%20%22wrong%22%20if%20you%20don't%20share%20the%20file%2C%20or%20at%20least%20part%20of%20it%2C%20after%20removing%20any%20private%20or%20confidential%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899408%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899408%22%20slang%3D%22en-US%22%3EI'll%20share%20the%20file%20cause%20im%20at%20frustration%20level%3C%2FLINGO-BODY%3E
Occasional 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

 

 

 

 

12 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

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