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

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