Looking for value in a column A

Solved/Closed
Bob - Mar 24, 2011 at 06:19 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Mar 24, 2011 at 09:13 AM
Hello,

I have to prepare an invoice and calculate the amount of shipping.

This is the lay out:
I have to compare the subtotal value, E16, in the invoice Worksheet
with another Worksheet named "shipping" and return in E17, the shipping costs

Shipping sheet:
Column A Column B
Subtotal Shipping ($)
100 50
1,000 25
2,000 10
3,000 5
5,000 0

So if the amount in subtotal E16 is equal or less than hundred the shipping is 50$
If it is equal or less than a 1000, 25 $ and so on.

I was wondering if anyone could suggest a formula using Vlookup & IF?

Is there a more simple solution?

Thanks,

Bob






2 replies

RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Mar 24, 2011 at 07:09 AM
use this formula

==LOOKUP(A2,{0,101,1001,2001,3001},{50,25,10,5,0})
1
Hi,
Thanks for your prompt response.
I tried your formula, and it works fine.
The only problem is that when the subtotal is 0, it returns #N/A.
If I convert your formula in:
=LOOKUP(A2,{0,1,101,1001,2001,3001},{0,50,25,10,5,0})
will it work or do I have to write an if statement:

=if(a1=0,"",=LOOKUP(A2,{0,101,1001,2001,3001},{50,25,10,5,0}))
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 24, 2011 at 09:13 AM
If you already have the sheet (as seem from your question, why not add two rows to it saying that shipping is 0 is sub total is 0 or a negative value, and then some thing real small to start shipping from) and keep it sorted, then you can use lookup

some thing like this
say data is

-
-40000	0
0.000001	50
100	50
1,000	25
2,000	10
3,000	5
5,000	0


=LOOKUP(D1,A1:A8,B1:B8)
0