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

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

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

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

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