# Simplified calculation for dimensional lookup

#### davey4444

##### Board Regular
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
ABCDEFGHIJK
1Recipe
2Ingredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8Ingredient 9Ingredient 10
3Product 1111
4Product 211
5Product 311

<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
ABCDEFGHIJKLM
8Variation
9CountryCountyTownIngredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8Ingredient 9Ingredient 10
10UK5101536924610
11France32459978631
12UKLancs
13UKLancsTown A
14UKLancsTown B
15UKLancsTown C
16UKYorks95
17UKYorksTown D
18UKYorksTown E234
19UKYorksTown F
20FranceVendee6899
21FranceVendeeTown G
22FranceVendeeTown H
23FranceVendeeTown I
24FranceBrittany
25FranceBrittanyTown J
26FranceBrittanyTown 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

Code:
``=(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!

Excel 2007
ABCDEFGHIJKLM
28Calculations
29
30Ingredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8Ingredient 9Ingredient 10TOTAL
31Product 1Town D9100050000024
32Product 2Town G30400000007

<tbody>
</tbody>
Sheet1

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
ABCDEFGHIJKLM
1Recipe
2Ingredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8Ingredient 9Ingredient 10
3Product 1111
4Product 211
5Product 311
6
7
8Variation
9CountryCountyTownIngredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8Ingredient 9Ingredient 10
10UK5101536924610
11France32459978631
12UKLancs
13UKLancsTown A
14UKLancsTown B
15UKLancsTown C
16UKYorks95
17UKYorksTown D
18UKYorksTown E234
19UKYorksTown F
20FranceVendee6899
21FranceVendeeTown G
22FranceVendeeTown H
23FranceVendeeTown I
24FranceBrittany
25FranceBrittanyTown J
26FranceBrittanyTown K
27
28Calculations
29
30Ingredient 1Ingredient 2Ingredient 3Ingredient 4Ingredient 5Ingredient 6Ingredient 7Ingredient 8Ingredient 9Ingredient 10TOTAL
31Product 1Town D9100050000024
32Product 2Town G30400000007
33
34
35ProductTownProduct RowTown RowCounty RowCountry RowTotal
36Product 1Town D187124
37Product 2Town G2121127
Sheet1
Cell Formulas
RangeFormula
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))

Replies
9
Views
511
Replies
1
Views
456
Replies
3
Views
370
Replies
11
Views
790
Replies
14
Views
753

1,196,216
Messages
6,014,043
Members
441,801
Latest member
Aneurysm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back