Locating values in a range and bringing up the corresponding price

[Zuo]

Hi,

I appreciate if somebody can help me with a formula. In one spread sheet I
have a list of products and their weights. In that same sheet I want to
create a formula that brings up the corresponding applicable freight $ based
on the range in which the weight value falls in the table below, which is on
another sheet in the same file. i.e. if the weight is 129 lbs then it should
bring up a freight cost of $227 if I am using the Bronze plan.


Weights and Corresponding Freight rates


Weight (lbs.) Bronze Silver Gold

0-100 $160 $175 $204
101-200 $227 $247 $261
201-300 $268 $282 $303
301-400 $300 $327 $352
401-500 $363 $409 $435
501-600 $413 $471 $496
601-700 $479 $548 $574
701-800 $537 $624 $653
801-900 $589 $713 $742
901-1,000 $642 $780 $839
1,001 lbs & over (cwt) $62 $77 $82

Kind Regards,

Zuo

 

[Duke Carey]

First, change your first column so that it shows the bottom end of the weight
range, and your table then looks like this

Weight Bronze Silver Gold
0 $160 $175 $204
101 $227 $247 $261
201 $268 $282 $303
301 $300 $327 $352
401 $363 $409 $435
501 $413 $471 $496
601 $479 $548 $574
701 $537 $624 $653
801 $589 $713 $742
901 $642 $780 $839

Assuming the column headings are in row 3, columns B:E and the table is in
B4:E13, then use the formula

=VLOOKUP([weight],B4:E13,LOOKUP([plan],{"Bronze","Silver","Gold"},{2,3,4}))

replace [weight] with the cell address where you enter the weight, and
likewise for [plan]

Not sure how you deal with weights in excess of 1,001

 

[Zuo]

Duke,

Thank you for your insight. It works! Regarding the over 1000 Lbs items
the rule is that IF the weight is over 1000, THEN divide the weight by 100
and multiply by the per pound $amount of the selected plan. I guess I have
to nest your VLookUP formula in an IF fuction? Can you show me what you
recommend?

Thank you again.

ZUO

 

[Zuo]

Duke,

I just noticed that the formula works well with the Bronze and the Silver
plans, however when I input Gold as a plan it returns the values of the
Bronze plan. Please advise.

Regards,

Zuo