Product Costing - looking up values based on name and calculating cost given components

[jellybeancounter]

I am using Excel 2010 and trying to build a formula to calculate how much each component of a recipe costs.

I have an ingredients list on one worksheet which contains the following:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Price[/TD]
[TD]Per:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD]Ingredient[/TD]
[TD]Average Price[/TD]
[TD]Qty[/TD]
[TD]Measure[/TD]
[TD]Oz[/TD]
[TD]tsp[/TD]
[TD]Tbsp[/TD]
[TD]Cup[/TD]
[/TR]
[TR]
[TD]Base[/TD]
[TD]All purpose flour[/TD]
[TD]5.00[/TD]
[TD]160[/TD]
[TD]oz[/TD]
[TD].03[/TD]
[TD].19[/TD]
[TD].06[/TD]
[TD].004[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a recipe on another worksheet which contains the following:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD]Measure[/TD]
[TD]Ingredient[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Pie Crust[/TD]
[TD]2.5[/TD]
[TD]Cup[/TD]
[TD]All purpose flour[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

I would like to insert a formula in the cost cell above which can go to the ingredient list and find the cost of the flour then look at how much is required on the recipe worksheet and calculate the cost. For example: I would like the formula to calculate the following for flour:
All purpose flour is $0.04 per cup and my recipe needs 2.5 cups => 2.5*.04=0.10 Cost

I have quite a few ingredients and many variations on quantity for various recipes and I would like to find a more time efficient method of calculating than entering manual formulas.

Thanks!

 

[AhoyNC]

Take a look at the INDEX and MATCH functions

Here are some links on how to use them.
https://www.youtube.com/watch?v=3C0PdOVuWCI
https://www.youtube.com/watch?v=bMWv0AN-hSs


In the example below you would just copy the formula down as needed.

Excel Workbook
	A	B	C	D	E
1	Name	Amount	Measure	Ingredient	Cost
2	Pie Crust	2.5	Cup	All purpose flour	0.01
3		2	tsp	Sugar	0.0125
recipe

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Category	Ingredient	Average Price	Qty	Measure	Oz	tsp	Tbsp	Cup
2	Base	All purpose flour	5	160	oz	0.03	0.19	0.06	0.004
3		Sugar	1.2	32	oz	0.0375	0.00625	0.08	0.096
Ingerd

 

[TheVillageIdiot]

Assume your reference table is on Sheet1, with the header row as you have it in row 2. First column is column A.

On Sheet2, I have your table as above starting in cell A1. In cell E2, I have the following formula:

=$B2*INDEX(Sheet1!$1:$1048576,MATCH($D2,Sheet1!$B:$B,0),MATCH($C2,Sheet1!$2:$2,0))

Seems to work for me, although I don't fully understand your pricing system.

 

[jellybeancounter]

Thank you - that worked perfectly!