VLOOKUP with numeric and alphanumeric values

[Guest]

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!

 

[T. Valko]

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.

 

[Guest]

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?

 

[David Biddulph]

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.

 

[T. Valko]

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.

 

[Guest]

Thanks so much again -- the COUNTIF formula seems to have worked.

 

[T. Valko]

You're welcome. Thanks for the feedback!