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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup returning wrong results 0
vlookup returning zeros 5
First row where a cell is zero 1
Cocatenate not showing leading zeros MS Excel 2003 6
Convert Number to Text for Index/Match 1
Function to Count Zip Code Digits With Leading Zero 2
Lookup references 3
With F1 value, look at A, if match or partial match return B 5

Back
Top