Calculate Rate based on Country Zones

[ali_plus]

Hi,

I am trying to create a simple chart for my team mates to calculate import rates based on country zone and weight increment. I have two tables like this:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Country[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Zone[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]US[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]UK[/TD]
[TD="align: center"]UK[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Canada[/TD]
[TD="align: center"]CN[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]France[/TD]
[TD="align: center"]FR[/TD]
[TD="align: center"]4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]

Increment​

[/TD]
[TD]

From​

[/TD]
[TD]

To​

[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.0[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]1.0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]1.1[/TD]
[TD="align: center"]2.0[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]2.1[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]2.6[/TD]
[TD="align: center"]5.0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]25[/TD]
[/TR]
</tbody>[/TABLE]

Now I have a calculation sheet which is very simple. My team will select the country from drop down list and enter the weight and all is calculated automatically.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Country[/TD]
[TD="align: center"]Zone[/TD]
[TD="align: center"]Chargeable Weight[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]charges[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]France[/TD]
[TD="align: center"]SUMIF( I already made it)
SUMIF(A2:A5,J2,C2:C5)[/TD]
[TD="align: center"]The Team will enter the Weight here[/TD]
[TD="align: center"]NEED THIS RATE BASED ON WEIGHT AND ZONE[/TD]
[TD="align: center"]=D2 * C2[/TD]
[/TR]
</tbody>[/TABLE]


Please help me create the formula in cell M2 as my mind is currently our of ideas. I know it must be something simple but workload and mind not working creatively.

Thanks.

 

[ali_plus]

Bump

 

[James006]

No such problem with 1 ...

=Index($e$1:$j$6,match(m2,$e$1:$e$6,1),match(l2,$e$1:$j$1,0))

 

[James006]

Pleased you could fix your problem ...