Vlookup text values
 

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

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

www.OfficeArticles.com


wrote in message
ups.com...
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

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


wrote in message
ups.com...
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

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!
==============================================


wrote in message
ups.com...
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