Using Search with either vlookup or match and index
 

I have a problem that I hope there is an answer to. I am trying to get
data from one spreadsheet into another. In the destination sheet, I
have the reference code to be used in the A column ( i.e. the string to
search for). The problem is that exists within another string in the
second spreadsheet. (i.e. my reference code is "John Smith", but the
second sheet has it listed as "Politician: John Smith". The
"Politician:" change every entry, so all I have is the name. Once I
get this, I will need to get the data in another cell. i.e. The
string exists in cell C55, but I want the data in cell D59. Is there a
way to do this?

Thanks,

Jeff


--
jlowenstein
------------------------------------------------------------------------
jlowenstein's Profile: http://www.excelforum.com/member.php...o&userid=25326
View this thread: http://www.excelforum.com/showthread...hreadid=389165

Jeff,

For the string John Smith in cell A1, try:

=INDEX('Sheet 2 Name'!D:D,MATCH("*" &A1,'Sheet 2 Name'!C:C,FALSE)+4)

HTH,
Bernie
MS Excel MVP


"jlowenstein"
wrote in message
...

I have a problem that I hope there is an answer to. I am trying to get
data from one spreadsheet into another. In the destination sheet, I
have the reference code to be used in the A column ( i.e. the string to
search for). The problem is that exists within another string in the
second spreadsheet. (i.e. my reference code is "John Smith", but the
second sheet has it listed as "Politician: John Smith". The
"Politician:" change every entry, so all I have is the name. Once I
get this, I will need to get the data in another cell. i.e. The
string exists in cell C55, but I want the data in cell D59. Is there a
way to do this?

Thanks,

Jeff


--
jlowenstein
------------------------------------------------------------------------
jlowenstein's Profile:
http://www.excelforum.com/member.php...o&userid=25326
View this thread: http://www.excelforum.com/showthread...hreadid=389165