Lookup

[peiling]

Hi
i need some help here.
i need to lookup rates into table 2 base on the destination and weight from table 1.
under table 1, to destination CDG, for weight more than 100kg will be 3.9 and for weight more than 500 will be 3.50
for intance, under table 2 dest to IST and weight is 320kg i need to look up from table 1 the rates which is $2.70 into table 2 .
anybody here know what should be done?

lookup.jpg


table 1
destination
/weight 0 -45 45 100 250 500 1000
CDG 100 20.45 4 3.9 3.8 3.5 3.2
IST 100 20.45 3 2.8 2.7 2.7 2.5
AMS 100 20.45 3.9 3.8 3.7 3.4 3.1
HAM 100 20.45 4.1 4 3.9 3.5 3.2


table 2
DEST weight RATES
CDG 100 (need to look up 3.90 from table 1)
IST 320 (need to look up 2.70 from table 1)
AMS 250 (need to look up 3.70 from table 1)
HAM 999 (need to look up 3.50 from table 1)

[dilipandey]

Hi Peiling,

Welcome to the forum.

You can use the below formula;-

=INDEX($B$2:$H$5,MATCH($L2,$A$2:$A$5,0),MATCH($M2,$B$1:$H$1,1))

Lookup - peiling.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[peiling]

thanks lots for your help .
really great!

[dilipandey]

You are welcome peiling....

cheers

Request you to mark this thread as [SOLVED], see the forum rules to do so :-
http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey

<click on below 'star' if this helps>

[peiling]

thanks
sorry i have one more question
regarding the 0 and -45kg
0 means minimum.
that means if weight is more than (100/20.45) = 4.90kg, it will falls under -45kg rates.
if the weight falls below 4.90kg it will falls under 0 (min) rates.
is it possible to add into the formula whereby when value (B - C) weight is lower it will falls under 0 and when weight is higher it will falls under -45kg?

[peiling]

thanks
sorry i have one more question
regarding the 0 and -45kg
0 means minimum.
that means if weight is more than (100/20.45) = 4.90kg, it will falls under -45kg rates.
if the weight falls below 4.90kg it will falls under 0 (min) rates.
is it possible to add into the formula whereby when value (B - C) weight is lower it will falls under 0 and when weight is higher it will falls under -45kg?

[dilipandey]

Hi Peiling,

See the attachment, green cell and let me know if this what you were looking for, else share your example. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Ooopss ... missed the attachment, here it is :-

Lookup - peiling.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[peiling]

Hi
thanks for your time.
but when weight is 1kg it is still not showing min rates.
let me elaborate more as below.

there is actually a minimum rates for each destination
to this place CDG, min is 100 and next weight break is 20.45 and if more than 45kg it is 4.00.
it means that if weight falls below 4.89 kg (100/20.45 = 4.89) then the rates will be 100
if weight falls above 4.89kg , the rates will be 20.45.
if weight is more than 45kg it will be 4.00 so on and so fore.

another example, for DEST IST min is 80 next weight break is 11 and more than 45kg is 3.00
it means that if weight falls below 7.27 kg (80/11 = 7.27) then the rates will be 80
if weight falls above 7.27kg , the rates will be 11.
if weight is more than 45kg it will be 3.00 so on and so fore.

Untitled.jpg

table 1
MIN -45 45 100 250 500 1000
CDG 100 20.45 4 3.9 3.8 3.5 3.2
IST 80 11 3 2.8 2.7 2.7 2.5
AMS 99 21 3.9 3.8 3.7 3.4 3.1
HAM 88 21.6 4.1 4 3.9 3.5 3.2


table 2
DEST weight RATES
CDG 1 need to look up 100 from table 1)
IST 8 need to look up 11 from table 1)
AMS 3 need to look up 99 from table 1)
HAM 6 need to look up 21.60 from table 1)

[dilipandey]

Hi Peiling,

I would suggest you to upload an excel workbook containing all possible scenarios so that I can formulate them all .. thanks.
Please provide your answers as well and I'll try to achieve them using formulas.


Regards,
DILIPandey

<click on below 'star' if this helps>

[peiling]

Hi may i know how can i upload exel files ?

[peiling]

finally found out how to attached excel files.
sorry that i am still new to this forum

pls find attached

[dilipandey]

Hi Peiling..

I have read your comments in column N.. could you explain the logic as well basis which you have identified the answers.. thanks.
Looks like.. You have obtained the results from column B and C only..

Regards,
DILIPandey

<click on below 'star' if this helps>