Vlookup text values

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

www.OfficeArticles.com


<[email protected]> wrote in message
news:[email protected]...
> 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


<[email protected]> wrote in message
news:[email protected]...
> 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!
==============================================


<[email protected]> wrote in message
news:[email protected]...
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

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


<[email protected]> wrote in message
news:[email protected]...
> 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


<[email protected]> wrote in message
news:[email protected]...
> 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!
==============================================


<[email protected]> wrote in message
news:[email protected]...
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