Search cells for text (wildcard, list of values), return value based on found matches

[begexcel]

Hello!

I have run into this problem. I tried to figure this out my own but after hour, I need to admit defeat...

Idea:
There is a text cell which contains string of text. I want to search this cell based on list what I have defined.
If there is a match, I want to return value - based on what it found.

To illustrate what I mean, I have created an example workbook. Here is though more explanation to avoid misunderstanding

I have list of search words and Column B is what I would like to return if that specific search word matches:

00listvalues.PNG

And here is the original data with comments, in column A I would like to have the formula where it would return country. "Desired outcome" is what should be correct, if I would perform this work manually.

00listvalues2.PNG

Attachment included:

Lookup.xlsx

Thank in advance for those who spend their time assisting me!

[NeedForExcel]

Maybe

=INDEX(List!$B$1:$B$7,MATCH(TRUE,SEARCH(List!$A$1:$A$7,Data!C2)>0,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[begexcel]

Maybe

=INDEX(List!$B$1:$B$7,MATCH(TRUE,SEARCH(List!$A$1:$A$7,Data!C2)>0,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

It works like a charm! Thank You! Much appreciated!

Reputation added