Help in an IF and VLOOKUP nested function

[dragonbonetattoo]

I need cell H39 to have a formula that calculates shipping costs. If the subtotal (cell H37) is greater than 200, than H39 should display 0. If H37 is less that 200, it needs to display the shipping cost based on the table named shipping_cost. I entered the formula =IF(H37>200,0,VLOOKUP(H37,shipping_cost,2,FALSE)) and I'm getting a #NA error. What did I leave out?

 

[Hui]

Dragonbonetattoo


Firstly, Welcome to the Chandoo.org Forums


Is the value in H37 in the first Column of the shipping_cost Range ?

Make sure that the Shipping_Cost range has a value less than the lowest value expected in H37

 

[dragonbonetattoo]

The value in H37 is 32.95. The shipping_cost table looks like this:

0 6.00

55 9.50

100 12.50

150 16.00

The order total is on the left, and the cost is on the right. Since the order is under 55, the shipping costs in H39 should read 9.50.


Thanks!

 

[dragonbonetattoo]

I misspoke. Since H37 is 32.95, H39 should be 6.00 as it is less than 55.

Thanks

 

[dragonbonetattoo]

The lowest value in H37 would be 0. DO I need to modify the shipping_cost table, or do I need to modify the formula?

Thanks

 

[NARAYANK991]

Hi ,


Use the following formula :


=IF(H37>200,0,LOOKUP(H37,shipping_cost))


If you want to use VLOOKUP , then you need to change it to :


=IF(H37>200,0,VLOOKUP(H37,shipping_cost,2,TRUE))


Narayan

 

[Hui]

=IF(H37>200,0,VLOOKUP(H37,Shipping_Cost,2,TRUE))