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