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")))

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")))

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")))

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")))

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")))

=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")))