Simplified calculation for dimensional lookup

[davey4444]

Hi, hopefully this all makes sense. All I am trying to do is to show how much various products cost to produce based on where the production is and the ingredients used.

This is how I have my recipes set out, with a 1 showing if the ingredient is used in that particular product -


Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	Recipe
2		Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8	Ingredient 9	Ingredient 10
3	Product 1	1	1			1
4	Product 2	1		1
5	Product 3	1							1

<tbody>
</tbody>

Sheet1​

Here I show the costs of each ingredient. Each ingredient will have a cost for that country however there may be exceptions where town or the county has a different cost compared to the country where it is.

In essence if there is a town cost then that is used, if not then a county cost and otherwise the country cost.


Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M
8	Variation
9	Country	County	Town	Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8	Ingredient 9	Ingredient 10
10	UK			5	10	15	3	6	9	2	4	6	10
11	France			3	2	4	5	9	9	7	8	6	31
12	UK	Lancs
13	UK	Lancs	Town A
14	UK	Lancs	Town B
15	UK	Lancs	Town C
16	UK	Yorks		9				5
17	UK	Yorks	Town D
18	UK	Yorks	Town E		2			3			4
19	UK	Yorks	Town F
20	France	Vendee			6		8		9		9
21	France	Vendee	Town G
22	France	Vendee	Town H
23	France	Vendee	Town I
24	France	Brittany
25	France	Brittany	Town J
26	France	Brittany	Town K

<tbody>
</tbody>

Sheet1​

This is where I calculate the costs by looking at each individual ingredient for that product / town combination and seeing whether special town or county costs are used, if not then default to the country one.

The formula for C31 is below and this is filled across through to M32

=(IF(INDEX(D$10:D$26,MATCH($B31,$C$10:$C$26,0))<>0,INDEX(D$10:D$26,MATCH($B31,$C$10:$C$26,0)),IF(INDEX(D$10:D$26,MATCH(INDEX($B$10:$B$26,MATCH($B31,$C$10:$C$26,0)),$B$10:$B$26,0))<>0,INDEX(D$10:D$26,MATCH(INDEX($B$10:$B$26,MATCH($B31,$C$10:$C$26,0)),$B$10:$B$26,0)),INDEX(D$10:D$26,MATCH(INDEX($A$10:$A$26,MATCH($B31,$C$10:$C$26,0)),$A$10:$A$26,0)))))*INDEX(B$3:B$5,MATCH($A31,$A$3:$A$5,0))

Now this code works fine but is very clunky and means that I have to look up each individual ingredient. Is there a more efficient way to complete this calculation either with formula or VBA as the scalability of it is causing headaches!

Thanks in advance.

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M
28	Calculations
29
30			Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8	Ingredient 9	Ingredient 10	TOTAL
31	Product 1	Town D	9	10	0	0	5	0	0	0	0	0	24
32	Product 2	Town G	3	0	4	0	0	0	0	0	0	0	7

<tbody>
</tbody>

Sheet1​

 

[deanst]

Hi,

I think this will help a little, but there is still probably a more elegant solution out there. My additions are in Rows 35-37.

Regards,
Dean.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Recipe
2		Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8	Ingredient 9	Ingredient 10
3	Product 1	1	1			1
4	Product 2	1		1
5	Product 3	1							1
6
7
8	Variation
9	Country	County	Town	Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8	Ingredient 9	Ingredient 10
10	UK			5	10	15	3	6	9	2	4	6	10
11	France			3	2	4	5	9	9	7	8	6	31
12	UK	Lancs
13	UK	Lancs	Town A
14	UK	Lancs	Town B
15	UK	Lancs	Town C
16	UK	Yorks		9				5
17	UK	Yorks	Town D
18	UK	Yorks	Town E		2			3			4
19	UK	Yorks	Town F
20	France	Vendee			6		8		9		9
21	France	Vendee	Town G
22	France	Vendee	Town H
23	France	Vendee	Town I
24	France	Brittany
25	France	Brittany	Town J
26	France	Brittany	Town K
27
28	Calculations
29
30			Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4	Ingredient 5	Ingredient 6	Ingredient 7	Ingredient 8	Ingredient 9	Ingredient 10	TOTAL
31	Product 1	Town D	9	10	0	0	5	0	0	0	0	0	24
32	Product 2	Town G	3	0	4	0	0	0	0	0	0	0	7
33
34
35	Product	Town	Product Row	Town Row	County Row	Country Row	Total
36	Product 1	Town D	1	8	7	1	24
37	Product 2	Town G	2	12	11	2	7
Sheet1

Cell Formulas
Range	Formula
C36	=MATCH(A36, $A$3:$A$5, 0)
D36	=MATCH(B36, $C$10:$C$26)
E36	=MATCH(INDEX($B$10:$B$26, D36),$B$10:$B$26, 0)
F36	=MATCH(INDEX($A$10:$A$26, D36), $A$10:$A$26, 0)
G36	=SUMPRODUCT(((INDEX($D$10:$M$26, $D36, 0)=0)*((INDEX($D$10:$M$26, $E36, 0)=0)*INDEX($D$10:$M$26, $F36, 0) + INDEX($D$10:$M$26, $E36, 0)) + INDEX($D$10:$M$26, $D36, 0)), INDEX($B$3:$K$5, $C36, 0))