Vlookup with "inversed" wildcard

excelbeo

New Member
Joined
Aug 18, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

excel wildcards.png


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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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)
 
Upvote 0
Hi ExcelBeo,

Is this what you wanted?

ExcelBeo.xlsx
ABCDEF
1ResponseRecodeKeywordSample Table
2Technical CollegeSchool/College/University/VocationalCollegeSchool/College/University/Vocational
3CollegeSchool/College/University/VocationalUniversitySchool/College/University/Vocational
4Can't spell Collage---No key match---BossThrough work
5UniversitySchool/College/University/VocationalWorkThrough work
6Thrown out of CollegeSchool/College/University/VocationalJobThrough work
7FriendsThrough friendInternshipThrough work
8My workplaceThrough workFriendThrough friend
9Hired by BossThrough work
10Saw advert---No key match---
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=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---")
 
Upvote 0
Hi ExcelBeo,

Is this what you wanted?

ExcelBeo.xlsx
ABCDEF
1ResponseRecodeKeywordSample Table
2Technical CollegeSchool/College/University/VocationalCollegeSchool/College/University/Vocational
3CollegeSchool/College/University/VocationalUniversitySchool/College/University/Vocational
4Can't spell Collage---No key match---BossThrough work
5UniversitySchool/College/University/VocationalWorkThrough work
6Thrown out of CollegeSchool/College/University/VocationalJobThrough work
7FriendsThrough friendInternshipThrough work
8My workplaceThrough workFriendThrough friend
9Hired by BossThrough work
10Saw advert---No key match---
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=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---")
 
Upvote 0
Hi ExcelBeo,

Is this what you wanted?

ExcelBeo.xlsx
ABCDEF
1ResponseRecodeKeywordSample Table
2Technical CollegeSchool/College/University/VocationalCollegeSchool/College/University/Vocational
3CollegeSchool/College/University/VocationalUniversitySchool/College/University/Vocational
4Can't spell Collage---No key match---BossThrough work
5UniversitySchool/College/University/VocationalWorkThrough work
6Thrown out of CollegeSchool/College/University/VocationalJobThrough work
7FriendsThrough friendInternshipThrough work
8My workplaceThrough workFriendThrough friend
9Hired by BossThrough work
10Saw advert---No key match---
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=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!
 
Upvote 0
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.
 
Upvote 0
Hi again,
I tried to formula but I just get "Childhood." I know I'm doing something wrong, but I cannot figure it out.
Screen Shot 2020-08-18 at 8.12.30 PM.jpg


I have to eventually add more options in the keyword/sample table
 
Upvote 0
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
ABCDEF
1ResponseRecodeKeywordSample Table
2Technical CollegeSchool/College/University/VocationalCollegeSchool/College/University/Vocational
3CollegeSchool/College/University/VocationalUniversitySchool/College/University/Vocational
4Can't spell Collage BossThrough work
5UniversitySchool/College/University/VocationalWorkThrough work
6Thrown out of CollegeSchool/College/University/VocationalJobThrough work
7FriendsThrough friendInternshipThrough work
8My workplaceThrough workFriendThrough friend
9Hired by BossThrough work
10Saw advert 
11My friend works at collegeSchool/College/University/Vocational, Through work, Through friend
12 
13 
Sheet1
Cell Formulas
RangeFormula
B2:B13B2=TEXTJOIN(", ",1,FILTER(F$2:F$1000,(E$2:E$1000<>"")*ISNUMBER(SEARCH(E$2:E$1000,A2)),""))
 
Upvote 0
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
ABCDEF
1ResponseRecodeKeywordSample Table
2Technical CollegeSchool/College/University/Vocational
3CollegeSchool/College/University/VocationalUniversitySchool/College/University/Vocational
4Can't spell Collage---No key match---BossThrough work
5UniversitySchool/College/University/VocationalWorkThrough work
6Thrown out of CollegeSchool/College/University/Vocational
7FriendsThrough friendInternshipThrough work
8My workplaceThrough workFriendThrough friend
9Hired by BossThrough workThrough Family
10Saw advert---No key match---Childhood
11CollegeSchool/College/University/Vocational
12Can't spell Collage---No key match---JobThrough work
13Hired by BossThrough work
14FriendsThrough friendCollegeSchool/College/University/Vocational
15My workplaceThrough work
16FriendsThrough friend
Sheet1 (2)
Cell Formulas
RangeFormula
B2:B16B2=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---")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top