Looking for value in a column A
Solved/Closed
Bob
-
24 Mar 2011 à 06:19
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 24 Mar 2011 à 09:13
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 24 Mar 2011 à 09:13
Related:
- Looking for value in a column A
- How to delete a column in word - Guide
- Excel count number of occurrences in a column - Guide
- Excel column to number - Guide
- Tweetdeck expand column - Guide
- Display two columns in data validation list but return only one - Guide
2 responses
RWomanizer
Posts
365
Registration date
Monday 7 February 2011
Status
Contributor
Last seen
30 September 2013
120
24 Mar 2011 à 07:09
24 Mar 2011 à 07:09
use this formula
==LOOKUP(A2,{0,101,1001,2001,3001},{50,25,10,5,0})
==LOOKUP(A2,{0,101,1001,2001,3001},{50,25,10,5,0})
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}))
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}))
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
24 Mar 2011 à 09:13
24 Mar 2011 à 09:13
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
=LOOKUP(D1,A1:A8,B1:B8)
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)
