Vlookup text values

[marin_michael]

Hi,

I am having a difficulty that has been bugging me for some time.

I have 14 digits SCC codes. Some start with 1-9, some start with 0's.
Numbers stating with 0's have to be set up as text or the 0 disappears.
Whenever I set up a number as text and I do a vlookup, those numbers
are not being recognized as matching by the vlookup function. My
question is: "how can I have a 14 digit number displaying zeros in
front and still be able to compare it to another spreadsheet with the
same number using the vlookup function. Please help.



Mike

 

[Anne Troy]

My suggestion: Format ALL of your SCC codes as "00000000000000", then you
won't have to worry about it.
*******************
~Anne Troy

www.OfficeArticles.com

 

[Damon Longworth]

All of the data in your lookup array need to have the same format. Where do
these numbers originate? I would format the column as text before the data
is inserted. Then the leading 0's will be preserved.


--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com

 

[RagDyeR]

You could try something like this:

=VLOOKUP(TEXT(A1,"00000000000000"),Sheet1!A1:B10,2,0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,

I am having a difficulty that has been bugging me for some time.

I have 14 digits SCC codes. Some start with 1-9, some start with 0's.
Numbers stating with 0's have to be set up as text or the 0 disappears.
Whenever I set up a number as text and I do a vlookup, those numbers
are not being recognized as matching by the vlookup function. My
question is: "how can I have a 14 digit number displaying zeros in
front and still be able to compare it to another spreadsheet with the
same number using the vlookup function. Please help.



Mike