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!