How to find and return a value based on more than one text value in a column in Google Sheets

1
votes

I am trying to extract relevant links from a huge list of links based on the text that is present in A:A. I have succeeded in extracting relevant links based on the value in A:A using the following formula: 

=ArrayFormula({"Profile";if(len("*"&A2:A&"*"),iferror(vlookup(substitute("*"&C2:C&"*"," ","-")&"-"&"*"&A2:A&"*",{regexextract(D2:D,"^.+/(.+)\..+$"),D2:D},2,)),)})

Here is the URL to the Google Sheet https://docs.google.com/spreadsheets/d/1Y1emSB2G2h_d1AIHNAqP6pIsG6-tK4sIIVCBGGjVd4g/edit?usp=sharing

The challenge I'm facing is that the formula returns blank results when a row in A:A contains more than one name i.e first and last names. I have tried all means but I can't get it to work when the value in the first column contains more than one name.

Please assist me if you know the solution to this.

1
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

1 Answers

0
votes

try:

={"Profile"; ARRAYFORMULA(IFNA((VLOOKUP(REGEXEXTRACT(LOWER(A2:A), TEXTJOIN("|", 1, 
 IFNA(REGEXEXTRACT(E2:E, LOWER(TEXTJOIN("|", 1, SUBSTITUTE(A2:A, " ", "|"))))))), {
 IFNA(REGEXEXTRACT(E2:E, LOWER(TEXTJOIN("|", 1, SUBSTITUTE(A2:A, " ", "|"))))), E2:E}, 2, 0))))}

0