How can I avoid a BLANK cell being interpreted as a ZERO?

[matt]

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

 

[Tom Ogilvy]

Try using

=VLOOKUP(Sheet1!A1,items,2,False)

Does this help?

 

[Nigel]

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]

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(items,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)),"",IF(LEN(TRIM(INDEX(items,MAT
CH(Sheet1!A1,INDEX(items,0,1),0),2)))=0,"",VLOOKUP(Sheet1!A1,items,2,FALSE))
)

--
Regards,
Tom Ogilvy

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?







----== 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

=---

 

[Tom Ogilvy]

I reread you post - This is what you want

=IF(Sheet1!A1="","",VLOOKUP(Sheet1!A1,items,2,FALSE))