Vlookup for data contained in a cell
 

Hello all,

I am working (reluctantly) with a table that was ported
over from Microsoft Word. Some of the cells (in the
left-most column) contain more than one data element
in each row (the data elements individually vary in length)

Example of one row in the original table (let's say at A1:B2):

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02
_1225_03

I created a new, identical table on a separate worksheet
(except each Col1 data element appears in a separate row) & am
attempting to vlookup data in Col2 so that each row
in this new table will correctly display the correct Col2 data in
the second column. Hence, the new table (based on a vlookup
on the original table) will display the above info as:

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02 Data1
_1225_03 Data1

on three separate rows.

The problem I am having is with this function (in each
corresponding row of Col2 in the new table):

=VLOOKUP("_1225_02",OrigTableSheet!$A$1:$B$20,2,FA LSE))

This will only work if the A1 cell in Sheet 'OrigTableSheet'
EXACTLY matches value "_1225_02". In this case, "_1225_02"
IS contained in the cell, but since the cell also contains other
data, VLOOKUP misses it completely. I also tried using
functions RIGHT (which would only "find" it if it were the last
data element in the cell) & LEFT (which would only "find" it
if it were first).

If anyone has any helpful input on this it would be much
appreciated.

Thanks in advance





--

did ;you try using <tsrue instead of <false

"Garbunkel" wrote in message
...
Hello all,

I am working (reluctantly) with a table that was ported
over from Microsoft Word. Some of the cells (in the
left-most column) contain more than one data element
in each row (the data elements individually vary in length)

Example of one row in the original table (let's say at A1:B2):

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02
_1225_03

I created a new, identical table on a separate worksheet
(except each Col1 data element appears in a separate row) & am
attempting to vlookup data in Col2 so that each row
in this new table will correctly display the correct Col2 data in
the second column. Hence, the new table (based on a vlookup
on the original table) will display the above info as:

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02 Data1
_1225_03 Data1

on three separate rows.

The problem I am having is with this function (in each
corresponding row of Col2 in the new table):

=VLOOKUP("_1225_02",OrigTableSheet!$A$1:$B$20,2,FA LSE))

This will only work if the A1 cell in Sheet 'OrigTableSheet'
EXACTLY matches value "_1225_02". In this case, "_1225_02"
IS contained in the cell, but since the cell also contains other
data, VLOOKUP misses it completely. I also tried using
functions RIGHT (which would only "find" it if it were the last
data element in the cell) & LEFT (which would only "find" it
if it were first).

If anyone has any helpful input on this it would be much
appreciated.

Thanks in advance





--

Hi!

Try this:

=VLOOKUP("*_1225_02*",OrigTableSheet!$A$1:$B$20,2, FALSE)

Biff

"Garbunkel" wrote in message
...
Hello all,

I am working (reluctantly) with a table that was ported
over from Microsoft Word. Some of the cells (in the
left-most column) contain more than one data element
in each row (the data elements individually vary in length)

Example of one row in the original table (let's say at A1:B2):

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02
_1225_03

I created a new, identical table on a separate worksheet
(except each Col1 data element appears in a separate row) & am
attempting to vlookup data in Col2 so that each row
in this new table will correctly display the correct Col2 data in
the second column. Hence, the new table (based on a vlookup
on the original table) will display the above info as:

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02 Data1
_1225_03 Data1

on three separate rows.

The problem I am having is with this function (in each
corresponding row of Col2 in the new table):

=VLOOKUP("_1225_02",OrigTableSheet!$A$1:$B$20,2,FA LSE))

This will only work if the A1 cell in Sheet 'OrigTableSheet'
EXACTLY matches value "_1225_02". In this case, "_1225_02"
IS contained in the cell, but since the cell also contains other
data, VLOOKUP misses it completely. I also tried using
functions RIGHT (which would only "find" it if it were the last
data element in the cell) & LEFT (which would only "find" it
if it were first).

If anyone has any helpful input on this it would be much
appreciated.

Thanks in advance





--

Thanks! That works great.

One more thing: Is it possible to
accomplish this by addressing the
cell rather than hard-coded values?
Hence:
=VLOOKUP("*_1225_02*",OrigTableSheet!$A$1:$B$20,2, FALSE)
becomes
=VLOOKUP(A2,OrigTableSheet!$A$1:$B$20,2,FALSE)

If there is a way to do this, please LMK.

Thanks again for your help!

--



"Biff" wrote:

Hi!

Try this:

=VLOOKUP("*_1225_02*",OrigTableSheet!$A$1:$B$20,2, FALSE)

Biff

"Garbunkel" wrote in message
...
Hello all,

I am working (reluctantly) with a table that was ported
over from Microsoft Word. Some of the cells (in the
left-most column) contain more than one data element
in each row (the data elements individually vary in length)

Example of one row in the original table (let's say at A1:B2):

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02
_1225_03

I created a new, identical table on a separate worksheet
(except each Col1 data element appears in a separate row) & am
attempting to vlookup data in Col2 so that each row
in this new table will correctly display the correct Col2 data in
the second column. Hence, the new table (based on a vlookup
on the original table) will display the above info as:

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02 Data1
_1225_03 Data1

on three separate rows.

The problem I am having is with this function (in each
corresponding row of Col2 in the new table):

=VLOOKUP("_1225_02",OrigTableSheet!$A$1:$B$20,2,FA LSE))

This will only work if the A1 cell in Sheet 'OrigTableSheet'
EXACTLY matches value "_1225_02". In this case, "_1225_02"
IS contained in the cell, but since the cell also contains other
data, VLOOKUP misses it completely. I also tried using
functions RIGHT (which would only "find" it if it were the last
data element in the cell) & LEFT (which would only "find" it
if it were first).

If anyone has any helpful input on this it would be much
appreciated.

Thanks in advance





--

OK, I figured it out. I got it to work with:

VLOOKUP(CONCATENATE("*",A2,"*"),OrigTableSheet!$A$ 1:$B$20,2,FALSE)

Thanks again Biff!

--



"Biff" wrote:

Hi!

Try this:

=VLOOKUP("*_1225_02*",OrigTableSheet!$A$1:$B$20,2, FALSE)

Biff

"Garbunkel" wrote in message
...
Hello all,

I am working (reluctantly) with a table that was ported
over from Microsoft Word. Some of the cells (in the
left-most column) contain more than one data element
in each row (the data elements individually vary in length)

Example of one row in the original table (let's say at A1:B2):

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02
_1225_03

I created a new, identical table on a separate worksheet
(except each Col1 data element appears in a separate row) & am
attempting to vlookup data in Col2 so that each row
in this new table will correctly display the correct Col2 data in
the second column. Hence, the new table (based on a vlookup
on the original table) will display the above info as:

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02 Data1
_1225_03 Data1

on three separate rows.

The problem I am having is with this function (in each
corresponding row of Col2 in the new table):

=VLOOKUP("_1225_02",OrigTableSheet!$A$1:$B$20,2,FA LSE))

This will only work if the A1 cell in Sheet 'OrigTableSheet'
EXACTLY matches value "_1225_02". In this case, "_1225_02"
IS contained in the cell, but since the cell also contains other
data, VLOOKUP misses it completely. I also tried using
functions RIGHT (which would only "find" it if it were the last
data element in the cell) & LEFT (which would only "find" it
if it were first).

If anyone has any helpful input on this it would be much
appreciated.

Thanks in advance





--

Hi!

Try it this way:

=VLOOKUP("*"&A2&"*",OrigTableSheet!$A$1:$B$20,2,FA LSE)

Biff

"Garbunkel" wrote in message
...
OK, I figured it out. I got it to work with:

VLOOKUP(CONCATENATE("*",A2,"*"),OrigTableSheet!$A$ 1:$B$20,2,FALSE)

Thanks again Biff!

--



"Biff" wrote:

Hi!

Try this:

=VLOOKUP("*_1225_02*",OrigTableSheet!$A$1:$B$20,2, FALSE)

Biff

"Garbunkel" wrote in message
...
Hello all,

I am working (reluctantly) with a table that was ported
over from Microsoft Word. Some of the cells (in the
left-most column) contain more than one data element
in each row (the data elements individually vary in length)

Example of one row in the original table (let's say at A1:B2):

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02
_1225_03

I created a new, identical table on a separate worksheet
(except each Col1 data element appears in a separate row) & am
attempting to vlookup data in Col2 so that each row
in this new table will correctly display the correct Col2 data in
the second column. Hence, the new table (based on a vlookup
on the original table) will display the above info as:

Col1 Col2
---------------------------------
_555_01 Data1
_1225_02 Data1
_1225_03 Data1

on three separate rows.

The problem I am having is with this function (in each
corresponding row of Col2 in the new table):

=VLOOKUP("_1225_02",OrigTableSheet!$A$1:$B$20,2,FA LSE))

This will only work if the A1 cell in Sheet 'OrigTableSheet'
EXACTLY matches value "_1225_02". In this case, "_1225_02"
IS contained in the cell, but since the cell also contains other
data, VLOOKUP misses it completely. I also tried using
functions RIGHT (which would only "find" it if it were the last
data element in the cell) & LEFT (which would only "find" it
if it were first).

If anyone has any helpful input on this it would be much
appreciated.

Thanks in advance





--