2

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

3

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

Worksheet ScreenShot

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.

  • @2by You're welcome. I just added an explanation if you need one. – robinCTS Jun 16 '18 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.