IF and SEARCH function

[midcoastchris04]

Hi Everyone!

I am trying to use this formula to search for multiple words in a column. I looked it up online and can't quite get it to work. Here is the formula I'm trying to use:

=IF(ISNUMBER(SEARCH("*warranty*",f2)), "warranty")), IF(ISNUMBER(SEARCH("*service*",f2)), "service", "")

I need to add multiple IF searches before my false statement.

Thanks in advance!

Chris

 

[RoryA]

Can there be more than one word matching in a cell, and, if so, what should happen? Also, do you have the list of words to look for in a range somewhere (would make maintaining the formula simpler) and should the return value for each match just be the word itself?

 

[midcoastchris04]

Hey Rory! No, there shouldn't be more than one word matching in a cell. These are system notations with specific reasons listed within non-unique text. The unique values (list of words/phrases) are:

warranty
service
purchase order
estimated delivery date
null

 

[RoryA]

You could use something like this:

=IFERROR(LOOKUP(2,1/SEARCH({"warranty","service","purchase order","estimated delivery date","null"},F2),{"warranty","service","purchase order","estimated delivery date","null"}),"")

or put those values into a range of cells and replace the two arrays with the cell references.

 

[midcoastchris04]

Thanks, Rory! Looks like it worked! Have a great day!