Help with Index Match

[xdavidgarza]

Hello Everyone,

I need help with my recipe template. I have an inventory sheet linked to my recipe template using index match. For example, if i input kosher salt under the ingredients in the Recipe section it will automatically give me the quantity of the case by weight and the price in the As Purchased section based on the information in the inventory sheet. But i would like to be able to get not just the weight under the quantity but by each box in the case. Right now i can get either the 36 or just the 12. Id like to be able to type in box, lb, oz, etc and pull the right value.
Any ideas?

Recipe AS PURCHASED
INGREDIENT QUANTITY UOM QUANTITY UOM PRICE
kosher salt 1 BOX 36 lb 36.25
Kosher Salt 1 LB 12 box 36.25

INVENTORY
Ingredients	Quantity	Unit	Quantity	Unit	Price
KOSHER SALT	36​	lbm	12	BOX	$ 36.25
BALSAMIC GLAZE	108.8​	ozm	4	EA	$ 50.91
BBQ SAUCE	640​	ozm	4	EA	$ 50.35
BUFFALO SAUCE	4​	ga	4	EA	$ 81.53
DEL PASADO CHEESE	20​	lbm	4	EA	$ 55.67
CHILI SAUCE	43.8​	lbm	6	EA	$ 53.24
SAMBAL	96​	ozm	12	EA	$ 50.84
SRIRACHA	120​	ozm	6	EA	$ 24.06
Lemon Grass	1	lbm	1​	CASE	$14.04
Tomato	40	EA	40​	EA	$32.44
Roma	25	lbm	1​	CASE	$31.68
Baby tomato	120	OZ	12​	EA	$29.40
Diced Tomato	5	lbm	1​	TRAY	$15.83
Cucumber	12	CT	12​	EA	$16.64
Arugula	4	lbm	1​	CASE	$14.88

Recipe Template
Recipe	As Purchased
Ingredient	Quantity	Uom	Quantity	UOM	Price
Kosher Salt	1​	lb	36​	lbm	36.25​
kosher salt	1​	Box	12	BOX	36.25​

 

[Kerryx]

Have no idea why you would want to to this as your recipe would cost based on qty or weight of material used , in the above you have 36 Ibs = 12boxes = 36.25.
In the recipe you have 1box=12 box=36.25.
In the current environment of shrinkflation it is best to manage by using Purchased UOM weight at least you should spot when a supplier tries to short change you on the weight. A box is always a box but its the weight of the box thats important

 

[xdavidgarza]

Because a box goes into a a single recipe. And other recipes have it by weight. If my boss makes a recipe with 1 box of kosher and 2 cups of black pepper to make recipe I don't want to have to go back and look at what is the actual weight of the box. I already did the work and separated everything by weight and by eaches.
The inventory sheets shows the case by the eaches(12) and by weight (36) so that I can index match the name kosher to the quantity depending on wether I use weight or eaches in a recipe. Is there anyway I can match, the cell where I have kosher and where I have either box or lb, and have multiple arrays in the index match to pull from either the weight of the item or by the each.
If someone can just help me do this. Obviously I know that doing it by the weight is ideal. But sometimes my supplier has items not by weight but by eaches and would make my life easier. Also when other make a recipe it can be by the each and not by weight. I've done my research for weeks and cannot seem to figure out a solution on how to do this.

Please if anyone can please help me figure this out

 

[Candyman8019]

Since you have 365 version, try XLOOKUP rather than index match. It's much simpler to use.

 

[xdavidgarza]

I've tried that but I don't know what I'm doing wrong. It gives me a value error or name error. Anyway someone can give me an example based on the example that I've provided. I'm just not sure what I'm doing wrong.
Like I mentioned. I'd like to type in the ingredient such as kosher salt, the quantity, the UOM such as by box or by actual weight. But I'd like to somehow match the kosher salts info from the inventory list but somehow make it match the UOM used in the recipe.

 

[Candyman8019]

Xlookup will work like this:

Book1.xlsx
	A	B	C	D	E	F
1	NVENTORY
2	Ingredients	Quantity	Unit	Quantity	Unit	Price
3	KOSHER SALT	36	lbm	12	BOX	$36.25
4	BALSAMIC GLAZE	108.8	ozm	4	EA	$50.91
5	BBQ SAUCE	640	ozm	4	EA	$50.35
6	BUFFALO SAUCE	4	ga	4	EA	$81.53
7	DEL PASADO CHEESE	20	lbm	4	EA	$55.67
8	CHILI SAUCE	43.8	lbm	6	EA	$53.24
9	SAMBAL	96	ozm	12	EA	$50.84
10	SRIRACHA	120	ozm	6	EA	$24.06
11	Lemon Grass	1	lbm	1	CASE	$14.04
12	Tomato	40	EA	40	EA	$32.44
13	Roma	25	lbm	1	CASE	$31.68
14	Baby tomato	120	OZ	12	EA	$29.40
15	Diced Tomato	5	lbm	1	TRAY	$15.83
16	Cucumber	12	CT	12	EA	$16.64
17	Arugula	4	lbm	1	CASE	$14.88
18
19	Recipe Template
20	Recipe	As Purchased
21	Ingredient	Quantity	Uom	Quantity	UOM	Price
22	Kosher Salt	1	lbm	36	BOX	36.25
Sheet1

Cell Formulas
Range	Formula
C22	C22	=XLOOKUP(A22,A3:A17,C3:C17)
D22	D22	=XLOOKUP(A22,A3:A17,B3:B17)
E22	E22	=XLOOKUP(A22,A3:A17,E3:E17)
F22	F22	=XLOOKUP(A22,A3:A17,F3:F17)