Need excel formula to compare cell contents with array of partial strings with special characters and return result in next column

Question

I have a column of html links, e.g.,

    Sheet 1, Column A:
     a href="http://junk.com">
     a href="http://example.com">
     a href='/relative-link/' target=''>

I want to compare a list of substrings in A:A on another sheet and return the value in B:B. So for example, my list on another sheet may be:

    Sheet2:
    Column A        Column B
    example.com      mine
    href='/         relative
    href="http      external

So I would need a formula that for the column of html links would return:

    Sheet1:
     Column A                                Column B (formula)
     a href="http://junk.com">               external
     a href="http://example.com">            mine
     a href='/relative-link/' target=''>     relative

Any ideas? I know how to use INDEX/MATCH for exact matches, but not sure how to do it with a partial substring match. Also, I don't know if the special characters in some of the substrings will require anything different than a single alphanumerical word.

Many thanks.

For older Excel version with no AGGREGATE function, inspired by @Gary'sStudent post (stackoverflow.com/a/37683342/4388883), you can try this formula: =VLOOKUP(LOOKUP(99^99,SEARCH(Sheet2!$A$1:$A$3,A1),Sheet2!$A$1:$A$3),Sheet2!$A$1:$B$3,2,0). However, this string href="http also shows in a href="http://example.com"> and therefore, the result is not ideal. I couldn't find a way to solve this but maybe @Gary'sStudent will show up and further assist you. — ian0411

Scott Craner Scott Craner · Accepted Answer · 2017-10-05T20:20:13

Use:

=INDEX(Sheet2!$B$1:$B$3,AGGREGATE(15,6,ROW(Sheet2!$A$1:$A$3)/(ISNUMBER(SEARCH(Sheet2!$A$1:$A$3,A1))),1))

This will return the first match on sheet 2, so put that list in order of priority.

Need excel formula to compare cell contents with array of partial strings with special characters and return result in next column

1 Answers