Calculations based on ranges in a VLOOKUP table

[hockeygirl33]

Hello,

I have a shipment table with costs for various weight ranges and there are more than 7 ranges, so I cannot use a NESTED IF. How do I create a formula that wil look at my shipment data and multiply it by the correct rate?

R A T E S (In Cents per Pounds)
MINIMUM WEIGHTS
Weight <500 500 1000 2000 5000 10000
COST/Lb $53.00 $0.19 $0.15 $0.14 $0.11 $0.1

So how do I write a formula to look up the correct rate for the range and multiply it by the shipment weight? For example, if the shipment weight is 900 lbs, it would fall in the 500+ weight category. I know how to use a VLOOKUP to look up specific values, but I don't know how to get it to look up the rate for data that falls within a range.

I've tried this formula with the CONCATENATE function, but to no avail. Please help!

=IF(AND(data>=500,data<1000),MAX(data*0.19,53),"") etc...

 

[hockeygirl33]

Thanks guys. I'll try it out. FYI I'm a lady and I root for THE TORONTO MAPLE LEAFS!

 

[hockeygirl33]

Hey Logansdad,
Can you send me that table. I don't think I need to use it for this particular project, but it might come in handy.

Thanks!