I have two worksheets that list, among other information, product codes.
These product codes are all 10 characters long, but some are entirely numeric
(i.e. 0123456789) while others are alphanumeric, in that some contain a
single letter in the code (i.e. 012345678X). This single letter is always X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep sorted in a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the list of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so that
they are "custom" cells with "type" 0000000000 so that it recognizes product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match with a
product code that contains single letters. The formula does not match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!

Excel doesn't recognize leading 0s in numbers. In order to *display* leading
0s and keep the value a numeric number you have to use a custom number
format. However, this allows for the *display* of leading 0s but Excel still
does not "see" them.

You should format your product codes (both lists) as TEXT. Then your lookup
formula should work.

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
I have two worksheets that list, among other information, product codes.
These product codes are all 10 characters long, but some are entirely
numeric
(i.e. 0123456789) while others are alphanumeric, in that some contain a
single letter in the code (i.e. 012345678X). This single letter is always
X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep sorted in
a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the list of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so that
they are "custom" cells with "type" 0000000000 so that it recognizes
product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match with
a
product code that contains single letters. The formula does not match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!

Many thanks, T. Valko -- very much appreciate the response.

My issue is that formatting these values as text removes leading zeroes. Is
there any other way that would keep the integrity of the data?

"T. Valko" wrote:

Excel doesn't recognize leading 0s in numbers. In order to *display* leading
0s and keep the value a numeric number you have to use a custom number
format. However, this allows for the *display* of leading 0s but Excel still
does not "see" them.

You should format your product codes (both lists) as TEXT. Then your lookup
formula should work.

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
I have two worksheets that list, among other information, product codes.
These product codes are all 10 characters long, but some are entirely
numeric
(i.e. 0123456789) while others are alphanumeric, in that some contain a
single letter in the code (i.e. 012345678X). This single letter is always
X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep sorted in
a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the list of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so that
they are "custom" cells with "type" 0000000000 so that it recognizes
product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match with
a
product code that contains single letters. The formula does not match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!

Formatting the value as text does NOT remove leading zeroes. The problem
will arise if the value is already stored as a number, in which case there
are no leading zeroes to display. You need to format the cell as text
BEFORE you put the code in. Then it will retain the leading zeroes.
--
David Biddulph

"Dan" wrote in message
...
Many thanks, T. Valko -- very much appreciate the response.

My issue is that formatting these values as text removes leading zeroes.
Is
there any other way that would keep the integrity of the data?

"T. Valko" wrote:

Excel doesn't recognize leading 0s in numbers. In order to *display*
leading
0s and keep the value a numeric number you have to use a custom number
format. However, this allows for the *display* of leading 0s but Excel
still
does not "see" them.

You should format your product codes (both lists) as TEXT. Then your
lookup
formula should work.

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
I have two worksheets that list, among other information, product codes.
These product codes are all 10 characters long, but some are entirely
numeric
(i.e. 0123456789) while others are alphanumeric, in that some contain a
single letter in the code (i.e. 012345678X). This single letter is
always
X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep sorted
in
a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the list
of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so
that
they are "custom" cells with "type" 0000000000 so that it recognizes
product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match
with
a
product code that contains single letters. The formula does not match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!

The problem is that in your 2 lists the 10 character NUMERIC codes are not
the same format. One is probably a TRUE numeric value while the other is a
TEXT value. VLOOKUP doesn't evaluate text numbers and numeric numbers as
being equal.

Try using COUNTIF. It evaluates text numbers and numeric numbers as being
equal. The equivalent to your lookup formula would be:

=IF(COUNTIF(Sheet1!$A$1:$A$500,A1),A1,"not found")

Hmmm...

If you're comparing 2 lists and one is longer than the other all you need to
do is test the shorter list against the longer list. You said one list was
14,000 rows and the other was 500. Your formula is testing the long list
against the short list.


--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
Many thanks, T. Valko -- very much appreciate the response.

My issue is that formatting these values as text removes leading zeroes.
Is
there any other way that would keep the integrity of the data?

"T. Valko" wrote:

Excel doesn't recognize leading 0s in numbers. In order to *display*
leading
0s and keep the value a numeric number you have to use a custom number
format. However, this allows for the *display* of leading 0s but Excel
still
does not "see" them.

You should format your product codes (both lists) as TEXT. Then your
lookup
formula should work.

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
I have two worksheets that list, among other information, product codes.
These product codes are all 10 characters long, but some are entirely
numeric
(i.e. 0123456789) while others are alphanumeric, in that some contain a
single letter in the code (i.e. 012345678X). This single letter is
always
X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep sorted
in
a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the list
of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so
that
they are "custom" cells with "type" 0000000000 so that it recognizes
product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match
with
a
product code that contains single letters. The formula does not match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!

Thanks so much again -- the COUNTIF formula seems to have worked.
"T. Valko" wrote:

The problem is that in your 2 lists the 10 character NUMERIC codes are not
the same format. One is probably a TRUE numeric value while the other is a
TEXT value. VLOOKUP doesn't evaluate text numbers and numeric numbers as
being equal.

Try using COUNTIF. It evaluates text numbers and numeric numbers as being
equal. The equivalent to your lookup formula would be:

=IF(COUNTIF(Sheet1!$A$1:$A$500,A1),A1,"not found")

Hmmm...

If you're comparing 2 lists and one is longer than the other all you need to
do is test the shorter list against the longer list. You said one list was
14,000 rows and the other was 500. Your formula is testing the long list
against the short list.


--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
Many thanks, T. Valko -- very much appreciate the response.

My issue is that formatting these values as text removes leading zeroes.
Is
there any other way that would keep the integrity of the data?

"T. Valko" wrote:

Excel doesn't recognize leading 0s in numbers. In order to *display*
leading
0s and keep the value a numeric number you have to use a custom number
format. However, this allows for the *display* of leading 0s but Excel
still
does not "see" them.

You should format your product codes (both lists) as TEXT. Then your
lookup
formula should work.

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
I have two worksheets that list, among other information, product codes.
These product codes are all 10 characters long, but some are entirely
numeric
(i.e. 0123456789) while others are alphanumeric, in that some contain a
single letter in the code (i.e. 012345678X). This single letter is
always
X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep sorted
in
a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the list
of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so
that
they are "custom" cells with "type" 0000000000 so that it recognizes
product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match
with
a
product code that contains single letters. The formula does not match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
Thanks so much again -- the COUNTIF formula seems to have worked.
"T. Valko" wrote:

The problem is that in your 2 lists the 10 character NUMERIC codes are
not
the same format. One is probably a TRUE numeric value while the other is
a
TEXT value. VLOOKUP doesn't evaluate text numbers and numeric numbers as
being equal.

Try using COUNTIF. It evaluates text numbers and numeric numbers as being
equal. The equivalent to your lookup formula would be:

=IF(COUNTIF(Sheet1!$A$1:$A$500,A1),A1,"not found")

Hmmm...

If you're comparing 2 lists and one is longer than the other all you need
to
do is test the shorter list against the longer list. You said one list
was
14,000 rows and the other was 500. Your formula is testing the long list
against the short list.


--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
Many thanks, T. Valko -- very much appreciate the response.

My issue is that formatting these values as text removes leading
zeroes.
Is
there any other way that would keep the integrity of the data?

"T. Valko" wrote:

Excel doesn't recognize leading 0s in numbers. In order to *display*
leading
0s and keep the value a numeric number you have to use a custom number
format. However, this allows for the *display* of leading 0s but Excel
still
does not "see" them.

You should format your product codes (both lists) as TEXT. Then your
lookup
formula should work.

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
I have two worksheets that list, among other information, product
codes.
These product codes are all 10 characters long, but some are
entirely
numeric
(i.e. 0123456789) while others are alphanumeric, in that some
contain a
single letter in the code (i.e. 012345678X). This single letter is
always
X.

One sheet contains roughly 500 product codes, which I have sorted in
ascending order; the other sheet has 14,000+ that I need to keep
sorted
in
a
particular way (by unit sales).

I am trying to determine which of the 500 product codes are in the
list
of
14,000. To do this, I have created the following VLOOKUP formula:

=VLOOKUP(A1,Sheet1!$A$1:$K$500,1,FALSE)

I reformatted the cells in the columns where product codes appear so
that
they are "custom" cells with "type" 0000000000 so that it recognizes
product
codes as neither solely alpha nor numeric.

Using this VLOOKUP formula, I get #N/A except where there is a match
with
a
product code that contains single letters. The formula does not
match
instances where the product codes are solely numeric.

Can someone help me solve this? TIA!