How to categorize a range of text data based on specific words in the sentence

[devdutt_dev]

hi,
i want to categorise a range of data ( colum(A) ) based on specifi
words that might be in the entire sentence

a1: not able to recieve mails in Outlook
a2: printer not working
a3: not able to print
a4: not able to send mails
a5: outlook

in this eg. there are 3 outlook issues and 2 printer issues

printer, print = printer (category)
mail,outlook = Outlook (category)

PLEASE HELP ME ....
(e-mail address removed)

 

[Max]

Try in B1:

=IF(AND(ISERROR(SEARCH("*outlook*",$A1)),ISERROR(SEARCH("*mail*",$A1)),ISERR
OR(SEARCH("*print*",$A1))),"-",IF(AND(ISERROR(SEARCH("*outlook*",$A1)),ISERR
OR(SEARCH("*mail*",$A1))),"Printer","Outlook"))

Copy B1 down

Col B should return the category as specified,
or "-", if it's other than "Printer" or "Outlook"

For the sample data posted in A1:A5,
B1:B5 returns:

Outlook
Printer
Printer
Outlook
Outlook

 

[devdutt_dev]

Thanks a ton Max, your formula works great, i was stuck in thi
situation for about a week now..
thanks a tonn
bye
devdut

 

[Max]

Pleased to hear that, devdutt!
Thanks for the feedback

 

[Aladin Akyurek]

=INDEX($H$1:$H$3,MATCH(1,MATCH("*"&$G$1:$G$3&"*",A1,0),0))

which must be confirmed with control+shift+enter instead of just with enter.

G1:H3 houses the following table of associations/correspondences:

Outlook Outlook
Print Printer
Mail Outlook