Just to supplement a little to Bruce's response ..

If the VLOOKUP's table_array is in text, we could leave the table_array
alone and convert the lookup values (presumably numbers keyed in manually)
into text so that it matches, viz.:

Instead of using either:

=IF(A2="","",VLOOKUP(A2,New!A:B,2,0))

or

=IF(A2="","",IF(ISNA(MATCH(A2,New!A:A,0)),
"Part# not found in New",VLOOKUP(A2,New!A:B,2,0)))

we could use something like:

=IF(A2="","",VLOOKUP(A2&"",New!A:B,2,0))

or

=IF(A2="","",IF(ISNA(MATCH(A2&"",New!A:A,0)),
"Part# not found in New",VLOOKUP(A2&"",New!A:B,2,0)))

(Lookup part number in A2 converted to text via joining it with a "")

Alternatively, if the Part# in the table_array always contains leading zeros
for 5 digits (say), i.e. a format of: 00000, then we could try something
like:

=IF(A2="","",VLOOKUP(TEXT(A2,"00000"),New!A:B,2,0) )

or

=IF(A2="","",IF(ISNA(MATCH(TEXT(A2,"00000"),New!A: A,0)),
"Part# not found in New",VLOOKUP(TEXT(A2,"00000"),New!A:B,2,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---