Using Search with either vlookup or match and index

[jlowenstein]

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

Thanks,

Jef

 

[Bernie Deitrick]

Jeff,

For the string John Smith in cell A1, try:

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

HTH,
Bernie
MS Excel MVP


"jlowenstein" <[email protected]>
wrote in message
news:[email protected]...