Identifying if a string contains multiple elements to divide into categories

[Iris Stein]

I have a huge excel file. One of the columns has a variety of about 40 different values. I am in need of a formula that would tell excel the following:

Check if [Cell] contains any of the following words and if it does call the category "x" but I need to do this for multiple categories as follows:

If [cell] contains "Android", call this cell a Smartphone
If [cell] contains "iOS", call this call a SF/iOS
If [cell] contains "Blackberry", call this cell Blackberry
If [cell]contains "Asha", call this cell Asha
If [cell] contains "Chrome", call this cell "Chrome
If [cell] doesn't contain any of these, call this cell "Feature PHone"

I've tried the following but am getting an error

=IF(ISNUMBER(SEARCH("Android",H2)),"Smartphone", IF(ISNUMBER(SEARCH("iOS", H2, "Smartphone/IOS", (ISNUMBER(SEARCH("Smart Phone",H2)),"Smartphone",(ISNUMBER(SEARCH("Jelly Bean",H2))),"Smartphone", (ISNUMBER(SEARCH("Windows",H2))),"Windows", (ISNUMBER(SEARCH("Blackberry",H2))),"Blackberry", "Feature Phone")

Can anyone tell me how to fix this? The file is very long so I placed a sample file in case it helps someone figure this out. Thanks in advance.

 

[Asheesh]

Hi Iris -

See the attached for more details...used the below formula

IFERROR(LOOKUP("Ω",IF(SEARCH($L$2:$L$7,H2),$M$2:$M$7)),"Feature Phone")

 

[Asheesh]

If you don't want to use helper columns...you can use the below as well

IFERROR(LOOKUP("Ω",IF(SEARCH({"Android";"iOS";"Smartphone";"Jelly Bean";"Windows";"Blackberry"},H2),{"Smartphone";"Smartphone/iOS";"Smartphone";"Smartphone";"Windows";"Blackberry"})),"Feature Phone")

 

[Iris Stein]

This was extremely helpful. Thank you!

 

[Asheesh]

Happy to help ..

 

[Nebu]

Hi:

You do not need an array formula for this , the following formula will work just fine.

=IFERROR(LOOKUP(2^15,SEARCH($L$2:$L$7,H2),$M$2:$M$7),"Feature Phone")

Thanks