Lookup value in a row based on keyword

Question

I'm trying to lookup a value in a row based on a keyword and return the value. Example:

Column 1                Column 2                Column 3                Result
http://1stexample.com   http://2ndexample.com   http://3rdexample.com   =LOOPUP for "1stexample" should return http://1stexample.com
http://1stexample2.com  http://2ndexample2.com  http://3rdexample2.com  =LOOPUP for "1stexample" should return http://1stexample2.com

Any way to archive this with an Excel functions (VLOOKUP, HLOOKUP, INDEX, MATCH, etc)?

Answer 1

You need to use MATCH() and INDEX():

Enter this formula in E2 and ctrl-enter/copy-paste/fill-down into the rest of the column:

=INDEX(A2:C2,MATCH("*"&D2&"*",A2:C2,0))

Explanation:

If the third argument of MATCH() is a 0 (exact match), then the find string can use wildcards. * means zero or more characters, so MATCH("*1stexample*",…,0) means match the first string containing the phrase 1stexample.