VLOOKUP Help!!!

[russbarsl]

Hi
I am looking for some help with VLOOKUP.

-----A----------------B-------------C -----------D----------E
1--Zone---------Minimum------100---------1000------2000
2--SFOA--------$12.85--------$0.063------$0.06----$0.055
3--SFOB--------$13.25--------$0.065-----$0.062----$0.057
4--SFOC
5
6
7
8—SFOA--------Formula
9---500

My formula in B8 reads =VLOOKUP(A8,A1:E4,3)*A9)
Should give me a result of $31.50

This project is for the shipping industry so let explain what I’m
trying to accomplish.
A9 would be the resulting weight of a shipment to be calculated.
C1, D1 & E1 is the weight per pound or LB.
C2 - $0.063 per LB is the charge for shipments of 100 LBS up to 999
LBS
D2- $0.06 per LB is the charge for shipments of 1000 LBS up to 1999
LBS
E2 - $0.055 per LB is the charge for shipments of 2000 LBS and up.
The breaking point for the minimum is about 204 LBS so any resulting
calculation that is
$12.85 or lower should revert to the minimum.
Now I can’t use the number $12.85 itself as the minimum but what ever
happens to be the minimum as the case in B3.
I’m not sure what combination of formulas to us so I hope someone can
help.

Thanks
Russ

 

[Frank Kabel]

Hi Russ
try the following formula (according to your example data):
=MAX(VLOOKUP(A8,A2:E7,MATCH(A9,C1:E1,1)+2,0)*A9,VLOOKUP(A8,A2:E7,2,0))

Some notes:
the formula will return an error for weight below 100 (as there is no
price for this). To adapt to this, change the formula as follows:
=IF(A9<100,VLOOKUP(A8,A2:E7,2,0),MAX(VLOOKUP(A8,A2:E7,MATCH(A9,C1:E1,1)
+2,0)*A9,VLOOKUP(A8,A2:E7,2,0)))
this returns the minimum fee for weight below 100

HTH
Frank

 

[russbarsl]

Hay Frank

WOW!!!!!!
Amaizing!!!!!
Ureka!!!!

I don't know what else to say.
If I didn't get this formula right I was afraid my brain was going t
bubble out of my ears. My last stumbling block is done!!

Thanks
Rus

 

[Frank Kabel]

Hi Russ

glad to be of some help
Frank