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

"Zuo" wrote in message
...
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" wrote:

One way to get it all ...
http://cjoint.com/?czazitAMok
Freight charge table.xls
Inspiring? hit YES below
--
Max
Singapore
---
"Zuo" wrote:
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