Lookup returns #NA when search value (text) has leading zeros.
 

I'm working with inventory where drawing numbers have leading zeros. I need
to lookup the drawing number from a different sheet and return the correct
part number. The lookup function is returning #N/A for all of the drawing
numbers that have leading zeros, the rest work just fine. The search values
are text type on both sheets. The return value is a general type (integer).
How can I get this to work?

If you for instance use

=VLOOKUP(A1,D2:E200,2,0)

where A1 holds the integer, then try

=VLOOKUP(TEXT(A1,"00000"),D2:E200,2,0)

where the number of zeros would be the number of digits you are using

Regards,

Peo Sjoblom

"M-Dickey" wrote:

I'm working with inventory where drawing numbers have leading zeros. I need
to lookup the drawing number from a different sheet and return the correct
part number. The lookup function is returning #N/A for all of the drawing
numbers that have leading zeros, the rest work just fine. The search values
are text type on both sheets. The return value is a general type (integer).
How can I get this to work?