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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Wally-sns

New Member
Joined
Jun 30, 2006
Messages
46
Both tables are now made into text format, but it doesnt seem to want to read the source table properly.
 

Wally-sns

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,066
Office Version
  1. 365
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

New Member
Joined
Sep 5, 2006
Messages
18
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)
 

Wally-sns

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

Forum statistics

Threads
1,176,629
Messages
5,904,128
Members
435,074
Latest member
McKay_S

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
Top