Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Garbunkel
 
Posts: n/a
Default 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





--

  #2   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

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





--



  #3   Report Post  
Biff
 
Posts: n/a
Default

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





--



  #4   Report Post  
Garbunkel
 
Posts: n/a
Default

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





--




  #5   Report Post  
Garbunkel
 
Posts: n/a
Default

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





--






  #6   Report Post  
Biff
 
Posts: n/a
Default

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





--






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP IF Cell Contains a Word within Text DAWN Excel Discussion (Misc queries) 3 April 5th 23 01:07 PM
VLOOKUP using a cell calculated with NOW returns Error Chris Berding Excel Worksheet Functions 2 August 21st 05 03:22 AM
Cell color based upon cell value My View Excel Discussion (Misc queries) 11 July 6th 05 03:59 AM
cell color index comparison MINAL ZUNKE New Users to Excel 1 June 30th 05 07:11 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM


All times are GMT +1. The time now is 01:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"