Vlookup with "inversed" wildcard

[excelbeo]

So I think I generally understand how the "*" wildcard with vlookup works, but I need an "inverse" wildcard.

Below is an example of my challenges:

I have a list of Responses (column B) (over 2000 responses) that vary significantly but I need to recode it based on the "sample table" For example, any time a person mentions "college" it is coded as "School - College/University/Vocational;" anytime a person mentions "Work" or "Internship" in their response, it is coded as "Through Work"

In a vlookup, my lookup_value is A2 ("technical college") and my table array is E1:F8, my col index is 2.


Any suggestions to get the those responses with the word "college" any where in the response to be coded as "School - College/University/Vocation?"

Thank you!

 

[Peter_SSs]

For our purposes, we are going to select one option.

Do you mean one of the solution options provided in this thread?
.. or do you mean only one of the options in an example like my cell B11, instead of all 3?

 

[excelbeo]

Do you mean one of the solution options provided in this thread?
.. or do you mean only one of the options in an example like my cell B11, instead of all 3?

Sorry for the lack of clarity in my response. I meant that the recode value (column B) can only have one (1) selection or value. So, unfortunately, I cannot use your B11 example. But I really appreciate your suggestion and that option.

 

[Peter_SSs]

I meant that the recode value (column B) can only have one (1) selection or value.

Thanks for the clarification.
In that case, you could use this even shorter version.

excelbeo 2020-08-19 1.xlsm
	A	B	C	D	E	F
1	Response	Recode			Keyword	Sample Table
2	Technical College	School/College/University/Vocational
3	College	School/College/University/Vocational			University	School/College/University/Vocational
4	Can't spell Collage	-			Boss	Through work
5	University	School/College/University/Vocational			Work	Through work
6	Thrown out of College	School/College/University/Vocational
7	Friends	Through friend			Internship	Through work
8	My workplace	Through work			Friend	Through friend
9	Hired by Boss	Through work				Through Family
10	Saw advert	-				Childhood
11	College	School/College/University/Vocational
12	Can't spell Collage	-			Job	Through work
13	Hired by Boss	Through work
14	Friends	Through friend			College	School/College/University/Vocational
15	My workplace	Through work
16	Friends	Through friend
Sheet2

Cell Formulas
Range	Formula
B2:B16	B2	=INDEX(FILTER(F$2:F$20,IFERROR(SEARCH(E$2:E$20,"|"&A2),0)>1,"-"),1)