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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]URLs[/TD]
[TD]Formula Column[/TD]
[TD]Reference URLs[/TD]
[TD]Reference Name[/TD]
[TD]Reference Column?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]www.site.com/page1[/TD]
[TD][/TD]
[TD]www.site.com/page1[/TD]
[TD]Page 1[/TD]
[TD]page1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]site.com/page1[/TD]
[TD][/TD]
[TD]www.site.com/page2[/TD]
[TD]Page 2[/TD]
[TD]page2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]http://www.site.com/page1/example[/TD]
[TD][/TD]
[TD]www.site.com/page3[/TD]
[TD]Page 3[/TD]
[TD]page3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]www.site.com/page2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]https://site.com/page2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]www.site.com/page3/example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!