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

[sbi85]

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 documents here is a link to a Google sheet to show what I mean: https://docs.google.com/spreadsheets/d/1W6TVjpbmvQFXOPWhG61J7lt_dkgkV57OO8C7teo2fP8/edit?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

 

[sbi85]

Just a note: The google sheet contains 2 worksheets. The second one has the keywords. (I wasn't sure if it's obvious or not)

 

[AlanY]

see attached

https://drive.google.com/file/d/0B1h-Y2fdTl08U2R5R0RkRUFWTXc/view?usp=sharing

 

[oldbrewer]

ceo

cmo

consultant

coordinator

director

executive

founder

head

manager

officer

owner

partner

president

vp

Marketing Manager

yes

Chief Executive Officer

yes

yes

Marketing Director

yes

tier

CEO

yes

ceo

1

President

yes

cmo

2

Director of Marketing

yes

consultant

-1

Managing Director

yes

coordinator

3

Director

yes

these are the terms

director

2

Owner

yes

in the first 35 job titles

executive

1

Account Executive

yes

founder

1

Chief Marketing officer

yes

head

2

Account Manager

yes

I have given them

manager

3

Digital Marketing Manager

yes

arbitrary tier gradings

officer

3

VP Sales & Marketing

yes

owner

1

Marketing Coordinator

yes

partner

1

Marketing Executive

yes

president

1

VP Marketing

yes

vp

2

Marketing

Sales Director

yes

Sales & Marketing Manager

yes

Account Director

yes

Senior Marketing Manager

yes

Head of Marketing

yes

Online Marketing Manager

yes

Sales Manager

yes

Vice President of Marketing

yes

Founder

yes

Director of Sales

yes

Vice President Marketing

yes

Partner

yes

Content Manager

yes

Manager

yes

CMO

yes

Consultant

yes

Vice President

yes

I searched for the titles in row 1 in all the roles in column A

you can re-order row 1 in more important first

eg executive, ceo, founder……………manager

then match the first "yes" in each row to the tier table to asign a tier

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sbi85]

@AlanY - this is brilliant, looks exactly what I wanted. Weird though, when I am putting the function into Excel (Excel 2011 for Mac) it gives me a #NAME? error. Maybe the ArrayFormula is not available there. But if needed I can continue working in Google Spreadsheets.

@oldbrewer - thanks for this. I see what you are trying to achieve but I fear that Row 1 will have to be a very long row due to the large amount of keywords I will need to use. For example "executive" can be in Chief Executive Officer and in Account Executive as well and they will be different tiers so I will have to use them as keywords. It might be good for a second step, but first I was just trying to see if the current keywords for tiers work (checking overlaps, and analysing the gaps). The next step will be to try to fill in the gaps with adding additional keywords, which, quite frankly, I am not sure how I can do without a long manual work.

Actually I might ask this maybe someone has an idea.
So now I know how the current keywords categorised the job titles into tiers, however there are a lot of them which are not covered. I would just start adding keywords manually based on the "3 No" rows one-by-one, but is there a better way to do this somehow with Excel?
Trying to find common words or collocations that would cover (most) of the individual job titles in terms of tiering? This might be more of a programming question already so I don't really have high hopes.
But you guys already helped me so much, so I am in a much better place to sort this task out. Thank you.

 

[AlanY]

@AlanY - this is brilliant, looks exactly what I wanted. Weird though, when I am putting the function into Excel (Excel 2011 for Mac) it gives me a #NAME? error. Maybe the ArrayFormula is not available there. But if needed I can continue working in Google Spreadsheets.

may be I should explained previously.

there are 4 name ranges defined within the spreadsheet,

Tier_1 - ='Tier keywords'!$B$3:$B$100
etc.

note that I'd used Tier_1 instead of Tier 1 as spaces are not allowed with range name, also you need to increase the $B$100 to cater for a longer list.

hope this help

 

[oldbrewer]

my suggestion will work once you decide what are equivalent "words"

eg executive, chief executive, CEO, etc etc
just give them all the same "tier" in the lookup table
so my "yes's" in the search table could with a tweak of the formula give you the TIER

anomalies will leap out but can be fixed on the fly by amending the lookup table and the search headings

explain why this will not help you so we can ponder further

 

[sbi85]

I just realised that it is possible someone might have overwritten what you inputted. I've never seen the underscores in the range name. Sorry for that.

And while I was cheerful after I saw the results, my joy was a bit premature. The formula that is still currently in the spreadsheet is doing kind of the opposite what I would like it to do.

Correct me if I am wrong but isn't this forumla checks if *Marketing Manager* (B4) is available in the Tier 1 (B) column?


=ArrayFormula(IF(ISNUMBER(MATCH("*"&$B4&"*",'Tier keywords'!B:B,0)),"Yes","No"))


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?

 

[oldbrewer]

I resign from this post as I did not read the question correctly. I thought we just had a very long list of every known job title in the world, and we were giving them tier numbers. Now it appears that tier numbers have already been assigned so what are we trying to do, so to speak......

 

[sbi85]

Not a problem. I think I may have did a poor job explaining it. But your contribution was insightful.

In the meantime I have managed to crack it with some help. Here is the final formula:

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