Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I avoid a BLANK cell being interpreted as a ZERO?
I have a price list of items on Sheet2 with product codes from 0 to
999 in column A and prices in the adjoining column B. I have called this price list "items". Now, when I try to do a vlookup function on a cell within Sheet1, I come up with a price regardless of whether that cell is blank or has a zero in it. My function looks like this: =VLOOKUP(Sheet1!A1,items,2) How can I stop excel interpreting blank cells as cells with zeros in them? Or how can I alter the function so that it doesn't try to match up a blank cell A1 with a Cell containing 0 in table "items"? I appreciate your help in advance. Matthew Dowling |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I avoid a BLANK cell being interpreted as a ZERO?
Try using
=VLOOKUP(Sheet1!A1,items,2,False) Does this help? -- Regards, Tom Ogilvy matt wrote in message om... I have a price list of items on Sheet2 with product codes from 0 to 999 in column A and prices in the adjoining column B. I have called this price list "items". Now, when I try to do a vlookup function on a cell within Sheet1, I come up with a price regardless of whether that cell is blank or has a zero in it. My function looks like this: =VLOOKUP(Sheet1!A1,items,2) How can I stop excel interpreting blank cells as cells with zeros in them? Or how can I alter the function so that it doesn't try to match up a blank cell A1 with a Cell containing 0 in table "items"? I appreciate your help in advance. Matthew Dowling |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I avoid a BLANK cell being interpreted as a ZERO?
Doesn't that determine the quality of the match between the test value and
the table, not the resultant value? I had a similar problem and incoporated an IF clause to test if returned the price was zero, however you still have the challenge of dealing with missing values. However maybe zero priced items are missing? "Tom Ogilvy" wrote in message ... Try using =VLOOKUP(Sheet1!A1,items,2,False) Does this help? -- Regards, Tom Ogilvy matt wrote in message om... I have a price list of items on Sheet2 with product codes from 0 to 999 in column A and prices in the adjoining column B. I have called this price list "items". Now, when I try to do a vlookup function on a cell within Sheet1, I come up with a price regardless of whether that cell is blank or has a zero in it. My function looks like this: =VLOOKUP(Sheet1!A1,items,2) How can I stop excel interpreting blank cells as cells with zeros in them? Or how can I alter the function so that it doesn't try to match up a blank cell A1 with a Cell containing 0 in table "items"? I appreciate your help in advance. Matthew Dowling ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I avoid a BLANK cell being interpreted as a ZERO?
True, I guess I wasn't clear on the situation. Here is a conditional
formula that worked for me. =IF(LEN(TRIM(INDEX(items,MATCH(Sheet1!A1,INDEX(ite ms,0,1),0),2)))=0,"",VLOOK UP(Sheet1!A1,items,2,FALSE)) Should to it. to suppress #N/A if the lookup value is not found: =IF(ISNA(MATCH(Sheet1!A1,INDEX(items,0,1),0)),"",I F(LEN(TRIM(INDEX(items,MAT CH(Sheet1!A1,INDEX(items,0,1),0),2)))=0,"",VLOOKUP (Sheet1!A1,items,2,FALSE)) ) -- Regards, Tom Ogilvy Nigel wrote in message ... Doesn't that determine the quality of the match between the test value and the table, not the resultant value? I had a similar problem and incoporated an IF clause to test if returned the price was zero, however you still have the challenge of dealing with missing values. However maybe zero priced items are missing? "Tom Ogilvy" wrote in message ... Try using =VLOOKUP(Sheet1!A1,items,2,False) Does this help? -- Regards, Tom Ogilvy matt wrote in message om... I have a price list of items on Sheet2 with product codes from 0 to 999 in column A and prices in the adjoining column B. I have called this price list "items". Now, when I try to do a vlookup function on a cell within Sheet1, I come up with a price regardless of whether that cell is blank or has a zero in it. My function looks like this: =VLOOKUP(Sheet1!A1,items,2) How can I stop excel interpreting blank cells as cells with zeros in them? Or how can I alter the function so that it doesn't try to match up a blank cell A1 with a Cell containing 0 in table "items"? I appreciate your help in advance. Matthew Dowling ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I avoid a BLANK cell being interpreted as a ZERO?
I reread you post - This is what you want
=IF(Sheet1!A1="","",VLOOKUP(Sheet1!A1,items,2,FALS E)) -- Regards, Tom Ogilvy matt wrote in message om... I have a price list of items on Sheet2 with product codes from 0 to 999 in column A and prices in the adjoining column B. I have called this price list "items". Now, when I try to do a vlookup function on a cell within Sheet1, I come up with a price regardless of whether that cell is blank or has a zero in it. My function looks like this: =VLOOKUP(Sheet1!A1,items,2) How can I stop excel interpreting blank cells as cells with zeros in them? Or how can I alter the function so that it doesn't try to match up a blank cell A1 with a Cell containing 0 in table "items"? I appreciate your help in advance. Matthew Dowling |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decimal number interpreted as a date | Excel Discussion (Misc queries) | |||
Avoid counting Blank cells as "0" | Excel Worksheet Functions | |||
How to avoid cell overlapping? | Excel Discussion (Misc queries) | |||
how can avoid considering of blank cells in IF function | Excel Worksheet Functions | |||
excel CSV is interpreted as HTML | Excel Discussion (Misc queries) |