VLOOKUP between 2 sources of data keeping leading zeros, numbers as text

[arrrmaty]

Ok, so I have about 15,000 parts that are all different lengths [between two and twenty (or more) places], and different formats i.e.- some with numbers only, some with numbers and letters, some with leading zeros, some with - and + symbols, some with all of the above, like the following list:

6806008G
0-7601-2570-92
6908041
03310
CDI-3336
258647234+74
023
7800609

These numbers are stored as text to preserve their part numbers because losing or adding a leading zero will change the part to something completely different that we have in our inventory. I need to look up these part numbers from a different spreadsheet to return the frequency and quantity that that part is ordered. The part numbers on that spreadsheet are formatted as text also, but are not showing them stored as such, i.e.- they don't have the error message for the "numbers only" part numbers saying "number stored as text" unless I double click in the cell. The vlookup formula works for the part numbers that have symbols and letters, but returns a "#N/A" for part numbers that only have numbers. How can I change the part numbers on the reference sheet to show the error message saying they are "numbers stored as text" without going through the 15,000 parts and double clicking the cells? If this does not make sense, please ask clarifying questions. I do not know how to use macros in excel yet so those solutions will not be helpful at this time. Thanks!

 

[GRTorres]

If both of your formats are in text, I would go with Index/Match:

= INDEX('place entire table you are looking up', MATCH('text you want to lookup', 'table again', 0), 'column of data you wish to return)

Example:
Lookup values in column A1:A10
Lookup Table with Data C1:H500
Data interested in Table Column F

INDEX($C$1:$H$500, MATCH($A1, $C$1:$C$500, 0), 4)

 

[arrrmaty]

Couldn't get that to work, although it looks right and looks like it would work- maybe if I had more time to mess with it. But I ended up getting it to work by using a trim formula on the part number column of the second spreadsheet and then copied the numbers and pasted the values of the trimmed part numbers. The vlookup formula worked after that and the second sheet began to recognize that the part numbers with "numbers only" were being "stored as text". Thanks for the suggestion though, I'll play around with it and see if I get it to work that way too.

 

[GRTorres]

try entering the in the formula bar and hitting:

ctrl + shift + enter