help with formula

<< Hi,

I need some expert help with creating a formula.

The Goal is to assign a dollar value based on weight of shipment.


col. A = weight ranges (>100#, 100#, 200#, 300#, 400#, etc)
col. B = cost per increment of 100. Example : cost of shipping less than 100# would be 35.50 , cost of shipping 100-199# would be 45.50, cost of shipping 200-299# = 55.5, etc.

My problem is assigning a dollar amount (35.50) to a range of weights 100-199 or 200-299, or applicable weight / cost . I want the spreadsheet to compare the weight in cell example D34 to a range of weights col.A and then assign the appropriate cost, col. B.

Make sense ? >>


First of all, set up a table like so:

Column A (start, say, from A2 going down)

0
100
200
300
400
etc.

Column B (start, say, from B2 going down)
35.50
45.50
55.50
65.50
etc.

Your formula will be

=vlookup(D34,$A$2:$B$50,2)

NOTE: adjust the range $A$2:$B$50 as necessary