Looking for value in a column A
Solved/Closed
Bob
-
Mar 24, 2011 at 06:19 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 24, 2011 at 09:13 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 24, 2011 at 09:13 AM
Related:
- Looking for value in a column A
- Display two columns in data validation list but return only one - Guide
- Excel copy column from one sheet to another automatically - Guide
- Which function is used to compare a cell value to an array of cells and return a value that matches the location of the value in the array, and is used when there are more than two columns in the array ✓ - Excel Forum
- How to increment numbers when value changes in another column? - Excel Forum
- Auto serial number in excel based on another column ✓ - Excel Forum
2 responses
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Mar 24, 2011 at 07:09 AM
Mar 24, 2011 at 07:09 AM
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 January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 24, 2011 at 09:13 AM
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
=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)
