column formatting with Vlookup

Wally-sns

New Member
Joined
Jun 30, 2006
Messages
46
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 ?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Both tables are now made into text format, but it doesnt seem to want to read the source table properly.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Code:
=VLOOKUP(MID(A1,2,LEN(A1)),lookup_range, column, true/false)
or:
Code:
'=VLOOKUP(SUBSTITUTE(A1,"'0",""),lookup_range, column, true/false)
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top