Getting Spill error...

[sanantonio]

Hi All,

I have a data table with our Meat / Fish lines. For systematic reasons some of are items have "Dummy" codes. These Dummy Codes are linked to "real codes". The Real codes and dummy codes are mixed into the same data table. I can't change the data table.

I've written a formula which looks a-okay to me, but I keep getting the irritating "The world will end because we think you have a circular formula" message and I get a #SPILL! that an iferror cannot overcome.

So the formula is if the first 5 value of the Item Desc is "Dummy" (All our dummy codes are called this) then lookup the description in that column to find the real item (Which will have the exact same name except for the "Dummy" at the beginning).
And return the item code.

So you can see for the ones where "Dummy" exists this works great, it's returning the real item code. Into my xlookup I then have if not found return the item number, and on the if (IE: If the first 5 characters don't say "Dummy" then return the item code. But for everything that doesn't answer "Yes" for the IF I get this #Spill.

Any ideas anyone?

 

[sanantonio]

Ideally need to resolve the spill and the circular nonsense!

 

[Fluff]

The false part of the formula should have an @ like you have for the iferror.

 

[sanantonio]

The false part of the formula should have an @ like you have for the iferror.

Yup! This and I managed to simplify the formula:

This works with the desired results. No circular message and no spill!

 

[Fluff]

Glad you sorted it & thanks for the feedback.