Vlookup to search within url

[MASOODAHMAD]

Hi,

Is there a way we can search for the names from the below given Data (urls):

Data (urls)

https://www.box.com/d872/o7j2qy53fh6sikp/myFirst_Report_Abaxis.pdf?dl=0
https://www.box.com/d872/1ftve998sj5rckn/myFirst_Report_ABC_Group.pdf?dl=0
https://www.box.com/d872/qe5qbwetpw0qral/myFirst_Report_Abrisa_Technologies.pdf?dl=0
https://www.box.com/d872/fzbfqzehh1oxuf0/myFirst_Report_Aesculapian.pdf?dl=0
https://www.box.com/d872/w12nxtswbagswwb/myFirst_Report_Airlite_Plastics.pdf?dl=0
https://www.box.com/d872/jz08ctfrht6p422/myFirst_Report_AltaLink.pdf?dl=0
https://www.box.com/d872/44kokwdbq8qdwfv/myFirst_Report_Barnes_Healthcare.pdf?dl=0
https://www.box.com/d872/l4s37h7fqef8zzj/myFirst_Report_Bausch_Health.pdf?dl=0
https://www.box.com/d872/x7ce27mwx8src1k/myFirst_Report_Beal_Service.pdf?dl=0
https://www.box.com/d872/2g1ndew9s367mk5/myFirst_Report_Bella_Vista.pdf?dl=0
https://www.box.com/d872/cs4q90ekqvnwlcy/myFirst_Report_BioBridge_Global.pdf?dl=0
https://www.box.com/d872/54h88yytcesr32w/myFirst_Report_Bloodworks_Northwest.pdf?dl=0

The names to be searched are in Column D, like:

Column D (Names have spaces not underscores)

Barnes_Healthcare
Airlite_Plastics
Bausch_Health
Bloodworks_Northwest
Abaxis
Abrisa_Technologies

etc

Any help would be much appreciated.
Thanks

 

[kweaver]

With your data in column A and the names in D, you could put this in another column (and fill down) to find the row where the name is found in the web address:

=MATCH("*"&D1&"*",$A$1:$A$12,0)

 

[MASOODAHMAD]

Thanks kweaver. I'll try this by creating a helper column by replacing spaces with underscores in the Names.

Can you suggest something as there's a small catch in the names i.e. they don't have underscores as in the URLs, like:

Barnes Healthcare
Airlite Plastics
Bausch Health
Bloodworks Northwest
Abaxis
Abaxis Limited
Abrisa Technologies

Also note names might have multiple words.
Secondly, first or last names might be common at some places.

Thanks again.

 

[kweaver]

What about:

=MATCH("*"&SUBSTITUTE(D1," ","_")&"*",$A$1:$A$12,0)

Unsure what you mean by the last 2 comments in your last post.

 

[MASOODAHMAD]

Thanks, I'll check this. I'm sure this will work well.

Have a great day

 

[MASOODAHMAD]