Setup
I have a list of user names in Worksheet 1. Username in column B. Examples:
- DefaultUser
- jsmith
- jdefaul
- btesting
In worksheet 2 I have a separate list of generic/commonly used name terms. Worksheet name = Controls, terms stored in column A. Examples:
- default
- admin
- test
Goal
In worksheet 1, I would like to construct a formula for each row/user name that is able to tell me if the username within that row has a substring match with any of the terms from my generic name terms list. Examples
- DefaultUser >> TRUE
- jsmith >> FALSE
- jdefaul >> FALSE (missing the 't')
- btesting >> TRUE
What I have tried so far
I have been googling for a good while now and all of various VLOOKUP, MATCH and INDEX formulas I have found and tried aren't doing what I want.
=MATCH("*"&Controls!A:A&"*",B5,0)>> which has given me results I can't even decipher=INDEX(B4,MATCH(“*”&Controls!A:A&”*”,B4,0))>> did not work at all (everything returned as #NAME?)=VLOOKUP(B4,Controls!A:A,1,TRUE)>> Was WAAAAY too loose on its matching, returning values with what appeared to be no rhyme or reason=VLOOKUP("*"&B4&"*",Controls!A:A,1,FALSE)>> was the closest but not quite there. It only returned exact matches, because it is treating the lookup value as the substring component instead of my generic terms list. If I could get the inverse behavior I'd be golden.
It feels like I am relatively close but can't quite reach the finish line. Any help would be greatly appreciated.
