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 |
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 |
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 =--- |
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 =--- |
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 |
All times are GMT +1. The time now is 07:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com