column formatting with Vlookup

[Wally-sns]

Hello all...

If i have a column of numbers that look like this:

'012535
'014578
'045452
'047887

Then i am trying to lookup those numbers in a table. However, i beleive the formating of the appostrophe and leading zero are messing things up. I need the leading zero.

Is there a way to format both tables so that they are identically formatted and then my lookup will work ?

 

[tony0682]

Convert the column numbers to text and they will retain the 0

 

[Wally-sns]

Both tables are now made into text format, but it doesnt seem to want to read the source table properly.

 

[tony0682]

Did you remove the apostrophe?

 

[Wally-sns]

yes....the only way it works is if i completely copy the number over to the source table....but i cant do that in this situation b/c there are many more items in the course table, than being looked up.

i tried, copy , paste special , formats....doesnt want to work

 

[Oaktree]

If A1 has '012535 and you're trying to match it against a list of numbers which includes 12535 (which you'd like to match against '012535), consider:

=VLOOKUP(A1+0,lookup_range, column, true/false)

The +0 will convert '012535 to the number 12535.

 

[maytas]

If A1 has '012535 and you're trying to match it against a list of numbers which includes 12535 (which you'd like to match against '012535), consider:

=VLOOKUP(A1+0,lookup_range, column, true/false)

The +0 will convert '012535 to the number 12535.

If this nor working try:

=VLOOKUP(MID(A1,2,LEN(A1)),lookup_range, column, true/false)

or:

'=VLOOKUP(SUBSTITUTE(A1,"'0",""),lookup_range, column, true/false)

 

[Wally-sns]

Oaktree----that didnt work...but thanks much for the immediate reply.

Maytas - that worked beautifully....now can you tell me what all that lingo is in there so i can learn ?