lookup combined with search to find keywords and then assign a cat

[jalbert1975]

I've been struggling with a project where I have to take hundreds of
spreadsheets with thousands of contact records. Each record has a field
called "Title" and a field called "Job Function". We have each contact's
specific title, but then we want to categorize them in the "Job Function"
field as an executive, director, supervisor, employee, or faculty. In order
to have to go through and read each title and individually assign a function,
I would like to program an IF or LOOKUP statement combined with a SEARCH
statement to look for key words in the title field and then assign them one
of the function titles. What am I doing wrong here? Right now, I get #VALUE
for any title except for president. I also need to do a lookup table,
because I have more than seven keywords I'd like to search for. Thanks, Jason

Title Function
President President
CEO #VALUE
VP #VALUE

=IF((SEARCH("president",M3)),"Executive",IF((SEARCH("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Executive","nothing")))

 

[Tom Ogilvy]

search returns an error if it isn't found, so it might be better to use countif

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNTIF(M2,"*ceo*"),"Executive",IF(COUNTIF(M3,"*vp*"),"Executive","nothing")))

However, you can only nest if statements 7 deep, so I don't know if this is
going to fill the bill so to speak. (countif is case insensitive)

 

[jalbert1975]

Tom,
It seems like my IF statement is case sensitive right now. I will try
adding the asterisks.
How could I do it using LOOKUP so that I could have more than seven search
statements?
Thanks, Jason

 

[jalbert1975]

Tom,
Yes, the COUNTIF does work better than search. Thanks

 

[jalbert1975]

Ok, so now that I've fixed the search part, how can I nest it in a LOOKUP
statement? Here's what I'm trying that's not working:
LOOKUP(M5,{(COUNTIF(M5,"*president*")),(COUNTIF(M5,"*ceo*")),(COUNTIF(M5,"*vp*")),(COUNTIF(M5,"*director*")),(COUNTIF(M5,"*supervisor*")),(COUNTIF(M5,"*manager*")),(COUNTIF(M5,"*engineer*")),(COUNTIF(M5,"*staff*"))},{"Executive","Executive","Executive","Director","Supervisor","Employee","Employee"})

 

[Tom Ogilvy]

=INDEX({"Executive","Executive","Executive","Director","Supervisor","Supervisor","Employee","Employee"},MATCH(1,CHOOSE({1,2,3,4,5,6,7,8},COUNTIF(M5,"*president*"),COUNTIF(M5,"*ceo*"),COUNTIF(M5,"*vp*"),COUNTIF(M5,"*director*"),COUNTIF(M5,"*supervisor*"),COUNTIF(M5,"*manager*"),COUNTIF(M5,"*engineer*"),COUNTIF(M5,"*staff*")),0))

Entered with Ctrl+Shift+Enter. Check the first array - you only had 7
responses to match 8 countifs. I added a Supervisor for Manager, but that
may not be correct.

 

[jalbert1975]

Tom,
Thanks so much. I would have never thought of this approach. Last
question: There will be a number of contacts with no job title. With your
formula, it returns #N/A when the "Title" field it's searching is blank. How
can I get it to leave the "Job Function" field blank when the "Title" field
is either blank or contains text that doesn't match any of my keywords?
Thanks, Jason

 

[Tom Ogilvy]

=IF(OR(M5="",SUM(COUNTIF(M5,{"*president*","*ceo*","*vp*","*director*","*supervisor*","*manager*","*engineer*","*staff*"}))=0),"",INDEX({"Executive","Executive","Executive","Director","Supervisor","Supervisor","Employee","Employee"},MATCH(1,CHOOSE({1,2,3,4,5,6,7,8},COUNTIF(M5,"*president*"),COUNTIF(M5,"*ceo*"),COUNTIF(M5,"*vp*"),COUNTIF(M5,"*director*"),COUNTIF(M5,"*supervisor*"),COUNTIF(M5,"*manager*"),COUNTIF(M5,"*engineer*"),COUNTIF(M5,"*staff*")),0)))