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]

I can also use Vlookup in K2. Instead I will change it to vlookup cause it will be more accurate. Plus I am thinking in M2 there will be a combination of nested IF's and OR e.g. =IF(OR(xxx something....

 

[James006]

Hello,

How are the rates ... linked to the Zones ?

Where are the Zones in your second table ?

 

[ali_plus]

Hi James, it is written as 1, 2 & 3 in cells G1, H1 & I1

 

[James006]

Hi James, it is written as 1, 2 & 3 in cells G1, H1 & I1

... OK ... But your first table shows 4 Zones (1,2,3,4) ... !

 

[ali_plus]

Yes I am taking tables as examples. You can consider a new column J1:J6 for Zone 4 sorry I am on phone otherwise I would've edited it.

 

[James006]

With your revised table ...

In cell K2 =MATCH(K2,$A$2:$A$5,0)

In cell N2 you will have =Index($G$1:$J$6,findRow,MATCH(L2,$A$1:$J$1,0))

Regarding how to replace FindRow ... I do not know how to read/use the rows of your Table 2

Hope this will help

 

[James006]

If my understanding is correct ...

With the weight located in cell M2, you should replace findRow by following formula:

MATCH(M2,$E$1:$E$6,1)

or your final formula in cell N2

=INDEX($E$1:$J$6,MATCH(M2,$E$1:$E$6,1),MATCH(L2,$E$1:$J$1,0))

HTH

 

[ali_plus]

If my understanding is correct ...

With the weight located in cell M2, you should replace findRow by following formula:

MATCH(M2,$E$1:$E$6,1)

or your final formula in cell N2

=INDEX($E$1:$J$6,MATCH(M2,$E$1:$E$6,1),MATCH(L2,$E$1:$J$1,0))

HTH

James Thank you. the formula is working but there is one problem. if the value is greater than E6 than the formula returns error #REF. I think i should calculate as well if the value are greater than E6 than it should automatically take values from last row. Anyone with a fix? if you want I can attach the source file as well

 

[ali_plus]

Bump for anyone who can help