Reverse LookUp

[lneidorf]

Hi there.

I've got a novel problem that I cannot seem to crack.

Two tabs. Tab1 contains a list of proper names, like so:

|NAME|FIRST|LAST|
|John Lennon|John|Lennon|
|Paul McCartney|Paul|McCartney|
|George Harrison|George|Harrison|
|Ringo Starr|Ringo|Starr|

On Tab2, I've got many rows with one column containing a long filepath with lots of garbage in it. But amongst that filepath appear the last names of these people. It might look something like this:

|PATH|
|\\NAS01\local\london\apple\abbey+road\starr\blahblah\blah|
|\\NAS01\local\liverpool\cavern\harrison\blahblah\blah|

I need to add a column on Tab2 that will look in the long path and provide a match of the name from amongst those appearing on Tab1 that it finds in the long path. So the result on Tab2 would be:

|PATH|NAME|
|\\NAS01\local\london\apple\abbey+road\starr\blahblah\blah|Ringo Starr|
|\\NAS01\local\liverpool\cavern\harrison\blahblah\blah|George Harrison|

Appreciate the help!

 

[DRSteele]

This might just do it:

	A	B	C	D
Worksheet 1
John Lennon	John	Lennon
Paul McCartney	Paul	McCartney
George Harrison	George	Harrison
Ringo Starr	Ringo	Starr
Worksheet 2
|\\NAS01\local\london\apple\abbey+road\starr\blahblah\blah|
|\\NAS01\local\liverpool\cavern\harrison\blahblah\blah|

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FCE4D6"]NAME[/TD]
[TD="bgcolor: #FCE4D6"]FIRST[/TD]
[TD="bgcolor: #FCE4D6"]LAST[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FCE4D6"]|PATH|[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]|\\NAS01\local\london\apple\abbey+road\starr\blahblah\blah|Ringo Starr|[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]|\\NAS01\local\liverpool\cavern\harrison\blahblah\blah|George Harrison|[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D10[/TH]
[TD="align: left"]{=A10&INDEX($A$3:$A$6,MATCH(TRUE,ISNUMBER(SEARCH($C$3:$C$6,A10))))&"|"}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D11[/TH]
[TD="align: left"]{=A11&INDEX($A$3:$A$6,MATCH(TRUE,ISNUMBER(SEARCH($C$3:$C$6,A11))))&"|"}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[lneidorf]

DRSteele,

Thanks for your reply.

I'm afraid the way I posted my data misled you. I could not figure out how to get my sample data to post in a grid, so I used the pipe character | to indicate columns. So sorry about that. I'll have to scour the forum for the proper method to post sample data in table format.

The result I'm looking for is the Names (Ringo Starr & George Harrison) to display in a separate column, next to the paths. So If cell A10 = "|\\NAS01\local\london\apple\abbey+road\starr\blahblah\blah|", then cell B10 = "Ringo Starr".

Thanks!

 

[lneidorf]

Okay,

I played around a bit and got what I need with this formula:
{=INDEX(Sheet1!A$2:A$5,MATCH(TRUE,ISNUMBER(SEARCH(Sheet1!C$2:C$5,A2))))}

Sheet1 contains the three columns of names and the Path data (and formulas) are on Sheet2.

This was a simplified example of my real data. The data I have extends to hundreds of rows. I'm wondering the best way to set up these array formulas to accommodate for an ever-growing list of rows. I had tried entire columns (i.e. using Sheet1!A:A instead of a defined range) to no avail. I then tried a large range (Sheet1!A$2:A$5000), but that too did not work. Not sure how to proceed so that I do not have to constantly update the array formula every time another row of data is added to either of the lists involved. Perhaps a dynamic named range?

Thanks again!