Advice on Excel

Cotta

Hi All,


I've a little Excel query and I can't quite solve. What I have is in column "A" a list of job titles and in column B I need to convert these into specific titles.


So for example if Column A mentions "football" I need to convert this into "footballer," if column A mentions Food I need to convert this into Chef otherwise I need to return "other".


How do I write this code as none of my formulas are working?

droopsnoot

=IF(ISNUMBER(SEARCH("food",A1)),"Chef",IF(ISNUMBER(SEARCH("football",E15)),"Footballer","Other"))


Apparently the ISNUMBER function is required because of how SEARCH works - see here: http://www.mrexcel.com/forum/excel-questions/659723-nested-if-statement-search-find-text.html

rmg1

You'd be better off having a lookup table somewhere and then using VLOOKUP or INDEX/MATCH to bring back the correct response for each term.

It would be quite easy to keep it up to date as well rather than having a (possibly) huge formula depending on the number of options.

droopsnoot

I wondered about using lookup, but if the OP wants any word or phrase containing "food" to come back as chef, would it mean you'd need to list every possible combination to make it work?

rmg1

Possibly, or you could use an array formula (that would take a while to calculate).
The good thing about using a lookup is that you could just add to it as necessary as new words came in.