VLOOKUP that needs to work as "contains" - creating a keyword list for a database

Hey guys,

I got a job to categorise the Job titles in our CRM database into 3 tiers and a negative tier (for lead grading purposes).
A previous colleague started building up a ruleset which uses keywords to get a certain job title into category: Tier 1, Tier 2, Tier 3 or Negative Tier

Now this keyword list misses a very large portion of the database so what I am trying to achieve first is to check if a job title was tiered already and which tier does it belong to.

Since I can't attach upload the file here is a link to a Google sheet to show what I mean (it has 2 worksheets, the second has the keywords): https://docs.google.com/spreadsheets...it?usp=sharing

Because of the keywords a simple VLOOKUP doesn't work. It would need some kind of wildcard lookup. Additionally I would want the function to fill in the columns near the every job title, saying if it is in Tier 1, 2, 3 or negative.
I have filled in the first 3 rows in the spreadsheet manually to show what I would to achieve.

Is this doable? Can anyone help me out with this?
Since I am a very beginner in excel I would really appreciate any help you can give

Have a great day!

Balint

Hi

You can use the following formula in 'Job Totle Database'!D4

Formula:

Please Login or Register  to view this content.

note_1: This formula is not an array formula.
note_2: See the difference for keyword President or Director.

Best regards

Hey Jose,

Thanks for the reply.
I think that your formula is actually doing the reverse what I would need.
Correct me if I am wrong, but isn't the formula checking if the job title is available in the keywords?

So with an example if we use your formula:
=IF(ISNUMBER(MATCH("*"&$B4&"*",'Tier keywords'!B:B,0)),"Yes","No")

isn't this checks if *Marketing Manager* (B4) is available in the Tier 1 (B) column?
If I am correct I actually need to opposite. So I need to check if there is keyword that exists in the the job title.

The formula should check if any keyword in the Tier 1 column exists in the B4 job title.
Let's assume that B4 is "Global Online Marketing Manager" and in the Tier 1 column there is "Marketing Manager". In this case the formula should return "Yes" as "marketing manager" as a keyword exists in the job title "Global Online Marketing Manager".

Is it clear this way what I want to achieve?

Try

=IF(ISNUMBER(LOOKUP(2^15,SEARCH('Tier keywords'!B$2:B$10,$B4))),"Yes","No")

Note, do NOT use the entire column for the Tier keywords!B column.
It needs to be a finite range exactly the size of the list of keywords.
It must not contain any blanks.

this works wonderfully.
excellent work.

Thakns a lot for the help Jonmo1

You're welcome.