Zone weight base rate - calculation excel formula

In C2 and copy down.

=INDEX(costs!$A$1:$J$43,MATCH('Daily Data'!A2,costs!$A$1:$A$43,1),MATCH('Daily Data'!$B$1:$B$50,costs!$A$1:$J$1,1))

Additional info to make this work.
Drop the term Zone in the headers. Numbers only unless you change the lookup to include Zone. Matchings need to be exactly the same.
As to the weights: Need to remove reference to Kg and only use the first number in the lookup table. Again, matchups need to be exactly the same

1 does not equal Zone 1

20 does not equal 20kg nor 20kg-40kg

Since there were no reference to other countries, I ignored your looks ups for Canada and US. If you use them, make sure they match also.

Really super thanks for your quick response.

I have few more doubts. Kindly clarify.

First Doubt:
In daily data sheet A column will come like 0.100, 0.150, 0.200, 0.300, 0.400, 0.500. It will automatically consider weight 0.500 (is it possible)

Second Doubt:
In daily data sheet A column will come like: (1.200 should consider 1.500) (1.700 should consider 2)

Third Doubt:
In daily data sheet A column will come like: (example 33, 35, 36, 40) should automatically consider "cost sheet" A41 row 21-44

I will remove kgs.

================
Again i simply explain you in Data sheet A row - 0.650 means automatically it will consider 1
2.100 automatically consider 2.5

0.500 slab for upto 20 next 21-44 slab next 45-70 slab thats it.

Pls sir help to solve it this...

Try in C2:

Please Login or Register  to view this content.

Drag down

Modify bebo's formula to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.