Complex formula with Convert formula pulling from Table columns not working

[DThib]

Help please!

My formula is working for all parts except the part labeled "Nuts".
My issue is if the value displayed as units does not match the stated units in the inventory then convert the value to the one listed from the one in the inventory.
Here is the formula:
=IF([@[Amount Needed]]="","",IF(NOT([@Unit]=(INDEX(Ingredients,MATCH($B4,Ingredients[Inventory Ingredient],0),6))),"Nuts",(INDEX(Ingredients,MATCH($B4,Ingredients[[#Data],[Inventory Ingredient]],0),9)*[@[Amount Needed]])))

The point I have gotten to so far uses this:
=Convert($C4,INDEX(Ingredients,MATCH($B4,Ingredients[Inventory Ingredient],0),5),$D4)
and I get #REF

Any help is appeciated

 

[jasonb75]

#REF! means an invalid range, best guess would be that the named range, 'Ingredients' is smaller than the table range, 'Ingredients[Inventory Ingredient]' and that the match found in the second range exceeds the boundary of the first.

At minimum, you will need to provide the proper addresses, e,g, A1:A10 for the named ranges and table formulas, and the location of the match for $B4 in the inventory ingredients list before we are able to provide a more detailed answer.

 

[DThib]

Hi Jason and thanks for responding.

The Table I am comparing is named "Ingredients" in the Table the second column "B" (Inventory Ingredients" is an ever growing list of items. The "H" is the cost showing in the other table. The unit of measure to be compared to the table where the formula sits is "H"
Ex.

Storage	Inventory Ingredient	Store/Vendor/ Brand	Cost of Package	Size of Package	Unit of Measure on Package	Unit Added?	Cost / Unit	Cost per Unit
A​	B​	C​	D​	E​	F​	G​	H​	I​
Fridge	Egg, Extra Large	Tendercrop Farms​	$ 3.99​	12​	egg​		$0.33​	$0.333 /egg​
Fridge	Egg, White Only	Tendercrop Farms​	$ 2.80​	18​	egg​		$0.16​	$0.156 /egg​
Fridge	Egg, Yolk Only	Tendercrop Farms​	$ 2.80​	18​	egg​		$0.16​	$0.156 /egg​
Fridge	Egg, Large	Tendercrop Farms​	$ 2.80​	18​	egg​		$0.16​	$0.156 /egg​
Dry	Baking Powder	Hannaford	$ 1.99	10​	oz​		$0.20​	$0.199 /oz​
Dry	Baking Soda	Hannaford	$ 0.89	16​	oz​		$0.06​	$0.056 /oz​

The table the match is comparing to is this table "Recipe":

Ingredient​	Amount Needed​	Unit​	Recipe Cost​	Unit Price​
A​	B​	C​	D​	E​
Flour, King Arthur AP - White	0.5	oz	$0.00​	$0.61
Milk, 2% (FairLife)	2	oz	$0.93​	$0.46

I hope that helps a little. Let me know what else you may need from me.

DThib

 

[jasonb75]

I hope that helps a little.

Not really any clearer, there is nothing in the top table that matches the bottom one. The error that you have tells me that the formula had a problem finding the correct result.
MATCH is working, but there is a problem with the range of INDEX. Comparing your formula to the sample data, I see potential for an entirely different error but if the formula is causing multiple errors then #REF! could be overriding the others.

This is purely a guess, based on what little sense I can make of the above. The first part of CONVERT must be a number, the second must be a valid unit of measure to covert from, the third a valid unit of measure to convert to. Both units of measure must be in the list shown in the the formula when you type it and must be of the same type, e.g. "oz" and "g", you can't convert weight to distance, etc. (that is not meant as an insult of your intelligence, sometimes stating what most people would see as obvious can cause the proverbial penny to drop).

=Convert($B4,$C4,INDEX(Ingredients,MATCH($A4,Ingredients[Inventory Ingredient],0),6))

If you are not able to do anything with that then it would be better if you repost your samples using XL2BB (link below) so that the formulas, named ranges, etc are all correctly and accurately preserved. 2 tables the size of those in your last post should be sufficient, but please make sure that that the second table contains data that can be correctly matched to the first, then enter your expected results (manually calculated, not formulas that don't work) where the formula should go and explain in your post how you got that result from the data shown.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[DThib]

Thanks.
No insult. I just grabbed a portion of the Ingredients table. It did not show the example.
My apologies. I'll see what I come up with.