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:

	A	B	C
1	Country	Code	Zone
2	USA	US	1
3	UK	UK	2
4	Canada	CN	3
5	France	FR	4

<tbody>
</tbody>

	D	E	F	G	H	I
1	Increment​	From​	To​	1	2	3
2	0.5	0.0	0.5	10	22	35
3	0.5	0.6	1.0	10	22	35
4	0.5	1.1	2.0	8	20	30
5	0.5	2.1	2.5	8	20	30
6	0.5	2.6	5.0	5	15	25

<tbody>
</tbody>

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.

	J	K	L	M	N
1	Country	Zone	Chargeable Weight	Rate	charges
2	France	SUMIF( I already made it) SUMIF(A2:A5,J2,C2:C5)	The Team will enter the Weight here	NEED THIS RATE BASED ON WEIGHT AND ZONE	=D2 * C2

<tbody>
</tbody>

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 ...