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!

 

[mikerickson]

You could use something like =IF(ISNUMBER(SEARCH("College",A2)), VLOOKUP("College",$F$2:$F$8, 1, False))
Which will take care of "college".

You could either nest all seven into one IF statment or use something like
=IF(ISNUMBER(SEARCH("College",SUBSTITUTE(A2,"School","College")), VLOOKUP("College",$F$2:$F$8, 1, False))

to consolidate equivalent keywords and reduce the number of nested IFs
(I personally think that, in this case, 7 helper columns looks neater than a 7 level nested IF)

 

[Toadstool]

Hi ExcelBeo,

Is this what you wanted?

ExcelBeo.xlsx
	A	B	C	D	E	F
1	Response	Recode			Keyword	Sample Table
2	Technical College	School/College/University/Vocational			College	School/College/University/Vocational
3	College	School/College/University/Vocational			University	School/College/University/Vocational
4	Can't spell Collage	---No key match---			Boss	Through work
5	University	School/College/University/Vocational			Work	Through work
6	Thrown out of College	School/College/University/Vocational			Job	Through work
7	Friends	Through friend			Internship	Through work
8	My workplace	Through work			Friend	Through friend
9	Hired by Boss	Through work
10	Saw advert	---No key match---
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(INDEX($F$2:$F$8,AGGREGATE(15,6,ROW($E$2:$E$8)-ROW($E$1)/(SEARCH($E$2:$E$8,A2)>0),1)),"---No key match---")

 

[excelbeo]

Hi ExcelBeo,

Is this what you wanted?

ExcelBeo.xlsx
	A	B	C	D	E	F
1	Response	Recode			Keyword	Sample Table
2	Technical College	School/College/University/Vocational			College	School/College/University/Vocational
3	College	School/College/University/Vocational			University	School/College/University/Vocational
4	Can't spell Collage	---No key match---			Boss	Through work
5	University	School/College/University/Vocational			Work	Through work
6	Thrown out of College	School/College/University/Vocational			Job	Through work
7	Friends	Through friend			Internship	Through work
8	My workplace	Through work			Friend	Through friend
9	Hired by Boss	Through work
10	Saw advert	---No key match---
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(INDEX($F$2:$F$8,AGGREGATE(15,6,ROW($E$2:$E$8)-ROW($E$1)/(SEARCH($E$2:$E$8,A2)>0),1)),"---No key match---")

 

[excelbeo]

Hi ExcelBeo,

Is this what you wanted?

ExcelBeo.xlsx
	A	B	C	D	E	F
1	Response	Recode			Keyword	Sample Table
2	Technical College	School/College/University/Vocational			College	School/College/University/Vocational
3	College	School/College/University/Vocational			University	School/College/University/Vocational
4	Can't spell Collage	---No key match---			Boss	Through work
5	University	School/College/University/Vocational			Work	Through work
6	Thrown out of College	School/College/University/Vocational			Job	Through work
7	Friends	Through friend			Internship	Through work
8	My workplace	Through work			Friend	Through friend
9	Hired by Boss	Through work
10	Saw advert	---No key match---
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IFERROR(INDEX($F$2:$F$8,AGGREGATE(15,6,ROW($E$2:$E$8)-ROW($E$1)/(SEARCH($E$2:$E$8,A2)>0),1)),"---No key match---")

Hi Mike and Toadstool,
Thank you both so much for your help! That was much faster than I was expecting.

I think Toadstool's suggestion is closest to what I'm looking for. So, If I want to expand this to the 2000+ responses, do I need to change anything in the formula or can I just copy this formula down?

again, thank you both!

 

[Toadstool]

You're welcome!

The Keyword and Sample Table are fixed so if you extend those you'll need to change the Recode formula in column B entries from $E$8 and $F$8 to the last row number added. Apart from that change you just copy down the formula in column B as far down as you have the last data in column A.

I copied it down to 3000 rows to test speed and it was still fast.

 

[excelbeo]

Hi again,
I tried to formula but I just get "Childhood." I know I'm doing something wrong, but I cannot figure it out.

I have to eventually add more options in the keyword/sample table

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Are you happy with the result in cell B8 of Toadstool's example. (In A8 "work" does not appear as a word by itself but is part of "workplace"). Similar thing in row 7.

2. Is it possible that there could be more than one keyword in the response? If so, do you want all results returned?

If the answers to all of the above are "yes" then you could also try this, copied down as far as you want.
Note that I have allowed for the keyword list to extend down as far as 1000. You can adjust that number if required, just make sure it is big enough for however long that list might be.

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			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			Job	Through work
7	Friends	Through friend			Internship	Through work
8	My workplace	Through work			Friend	Through friend
9	Hired by Boss	Through work
10	Saw advert
11	My friend works at college	School/College/University/Vocational, Through work, Through friend
12
13
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=TEXTJOIN(", ",1,FILTER(F$2:F$1000,(E$2:E$1000<>"")*ISNUMBER(SEARCH(E$2:E$1000,A2)),""))

 

[Toadstool]

Hi again,
I tried to formula but I just get "Childhood." I know I'm doing something wrong, but I cannot figure it out.
View attachment 20640

I have to eventually add more options in the keyword/sample table

The missing keywords generate a hit so you can either remove all empty Keywords or replace with a check, such as this:

ExcelBeo.xlsx
	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	---No key match---			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	---No key match---				Childhood
11	College	School/College/University/Vocational
12	Can't spell Collage	---No key match---			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
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(INDEX($F$2:$F$19,AGGREGATE(15,6,ROW($E$2:$E$19)-ROW($E$1)/($E$2:$E$19<>"")*(SEARCH($E$2:$E$19,A2)>0),1)),"---No key match---")

 

[excelbeo]

Peter,
Thank you for providing your suggestion. For our purposes, we are going to select one option.

Toadstool,
Thanks for the two options. Those worked. I appreciate it. I really need to learn how to use these kinds of formulas. I'm blown away with everyone's knowledge and willingness to help.