Finding the corresponding value in a range?

[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.
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. Additionally IF the weight is over 1000,
THEN divide the weight by 100
and multiply by the per pound $amount of the selected 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

 

[Max]

One way to get it all ...
http://cjoint.com/?czazitAMok
Freight charge table.xls
Inspiring? hit YES below

 

[Zuo]

Max,

Thanks for your very detailed answer, very professional! I have a question
though, Duke had proposed the following solution which seems simpler but had
a problem which I could not resolve, see below. Is this a better solution
for the first part of the table? If so, how do I resolve the problem with
his formula?

“Duke†wrote:

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]

“Zuo†wrote:

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.

 

[Max]

Zuo, ask your new query in a fresh thread if Duke did not respond further in
that thread. Here, does my solution address ALL of your specs which you asked
in your original posting here? If so, take a moment, close it off, hit the
YES in that earlier response. Thanks.

 

[Fred Smith]

Lookup requires that values be in ascending order. So you need:
=VLOOKUP([weight],B4:E13,LOOKUP([plan],{"Bronze","Gold","Silver"},{2,4,3}))

Regards,
Fred

Max,

Thanks for your very detailed answer, very professional! I have a
question
though, Duke had proposed the following solution which seems simpler but
had
a problem which I could not resolve, see below. Is this a better solution
for the first part of the table? If so, how do I resolve the problem with
his formula?

“Duke†wrote:

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]

“Zuo†wrote:

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.

One way to get it all ...
http://cjoint.com/?czazitAMok
Freight charge table.xls
Inspiring? hit YES below