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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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

ADVERTISEMENT

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,016
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,141,630
Messages
5,707,511
Members
421,511
Latest member
mgroah1

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