Question on using SEARCH formula to search for multiple strings in a cell

**dcgrove** · 03-28-2012, 10:24 AM

Hello, is it possible to use the OR operator in the "Find Text" value of a SEARCH formula? The formula below is what I am trying use, but it is returning "Active" because the inclusion of the OR operator causes an #VALUE! error. If this is not possible, is there another or better way to search a cell for either "archive" or "inactive" strings?

=IFERROR(SEARCH(OR("archive","inactive"),H2),"Active")

Thanks!
Clayton Grove

**Dennis7849** · 03-28-2012, 10:49 AM

So let's say we have your string values in column A. In column B we want to put some functions to tell us if certain sub-strings exist in the A column data.

You could use this

Please Login or Register  to view this content.

**icestationzbra** · 03-28-2012, 10:50 AM

see if this helps:

=IFERROR(IFERROR(SEARCH("archive",A1),SEARCH("inactive",A1)),"active")

**JBeaucaire** · 03-28-2012, 05:41 PM

SEARCH() returns a numeric answer, so you really want to add an OR() and an ISNUMBER() test to see if any of them returned a number:

=IF(OR(ISNUMBER(SEARCH({"archive","inactive"}, H2))),"Active","")