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

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

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

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

lookup combined with search to find keywords and then assign a
 

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

"Tom Ogilvy" wrote:

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

lookup combined with search to find keywords and then assign a
 

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

"jalbert1975" wrote:

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

"Tom Ogilvy" wrote:

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

lookup combined with search to find keywords and then assign a
 

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,"*dire ctor*")),(COUNTIF(M5,"*supervisor*")),(COUNTIF(M5, "*manager*")),(COUNTIF(M5,"*engineer*")),(COUNTIF( M5,"*staff*"))},{"Executive","Executive","Executiv e","Director","Supervisor","Employee","Employee "})

"jalbert1975" wrote:

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

"jalbert1975" wrote:

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

"Tom Ogilvy" wrote:

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

lookup combined with search to find keywords and then assign a
 

=INDEX({"Executive","Executive","Executive","Direc tor","Supervisor","Supervisor","Employee","Employe e"},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.

--
Regards,
Tom Ogilvy


"jalbert1975" wrote:

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,"*dire ctor*")),(COUNTIF(M5,"*supervisor*")),(COUNTIF(M5, "*manager*")),(COUNTIF(M5,"*engineer*")),(COUNTIF( M5,"*staff*"))},{"Executive","Executive","Executiv e","Director","Supervisor","Employee","Employee "})

"jalbert1975" wrote:

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

"jalbert1975" wrote:

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

"Tom Ogilvy" wrote:

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

lookup combined with search to find keywords and then assign a
 

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" wrote:

=INDEX({"Executive","Executive","Executive","Direc tor","Supervisor","Supervisor","Employee","Employe e"},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.

--
Regards,
Tom Ogilvy


"jalbert1975" wrote:

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,"*dire ctor*")),(COUNTIF(M5,"*supervisor*")),(COUNTIF(M5, "*manager*")),(COUNTIF(M5,"*engineer*")),(COUNTIF( M5,"*staff*"))},{"Executive","Executive","Executiv e","Director","Supervisor","Employee","Employee "})

"jalbert1975" wrote:

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

"jalbert1975" wrote:

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

"Tom Ogilvy" wrote:

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))

lookup combined with search to find keywords and then assign a
 

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

--
Regards,
Tom Ogilvy


"jalbert1975" wrote:

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" wrote:

=INDEX({"Executive","Executive","Executive","Direc tor","Supervisor","Supervisor","Employee","Employe e"},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.

--
Regards,
Tom Ogilvy


"jalbert1975" wrote:

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,"*dire ctor*")),(COUNTIF(M5,"*supervisor*")),(COUNTIF(M5, "*manager*")),(COUNTIF(M5,"*engineer*")),(COUNTIF( M5,"*staff*"))},{"Executive","Executive","Executiv e","Director","Supervisor","Employee","Employee "})

"jalbert1975" wrote:

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

"jalbert1975" wrote:

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

"Tom Ogilvy" wrote:

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

=IF(COUNTIF(M3,"*president*"),"Executive",IF(COUNT IF(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)

--
Regards,
Tom Ogilvy




"jalbert1975" wrote:

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((SEARC H("ceo",M3)),"Executive",IF((SEARCH("vp",M3)),"Exe cutive","nothing")))