Partial Text Match Lookup

[Randy_S_84]

I am normally able to solve these issues with some help from Google, but can't seem to find an answer for this problem. Any help would be appreciated.

On one sheet I have a large amount of data containing a string of website URLs. Unfortunately the formatting of the URLs varies. On another sheet I have a list of properly formatted URLs and a name associated with the specific URL. Basically I want a formula in each cell of column B to search (C) using the unique page identifier (e.g. page1) in (A) to find the partial match and insert the corresponding name (D). I have tried to also use a reference column (E) to help the look-up, but haven't had much luck. I am unable to remove characters from either side of the URL because of the many variations. Below is an example (combined into 1 sheet for simplicity):

	A	B	C	D	E
1	URLs	Formula Column	Reference URLs	Reference Name	Reference Column?
2	www.site.com/page1		www.site.com/page1	Page 1	page1
3	site.com/page1		www.site.com/page2	Page 2	page2
4	http://www.site.com/page1/example		www.site.com/page3	Page 3	page3
5	www.site.com/page2
6	https://site.com/page2
7	www.site.com/page3/example

<tbody>
</tbody>

To over-explain, the formula in B2 would use the data in A2 to search C:C (or E:E) and insert the corresponding name from D:D, and so on for the rest of column B.

If there is a way (may include multiple steps) to do this please help.

 

[Oaktree]

Welcome to the board.

Try this in B2:

 =INDEX($E$2:$E$4,MATCH(TRUE,ISNUMBER(FIND($E$2:$E$4,A2)),0))

Note that this is an array formula and must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER. Doing so correctly will result in Excel putting { }s around your formula in the formula bar.

 

[Randy_S_84]

This worked perfectly (though I had to switch the first $E$2:$E$4 with $D$2:$D$4)! Thanks so much!