How to use Conditional formatting for multiple text in cell range?

[JulianS96]

Hi forum,
Just wondering how to conditional format multiple text in a given range.
What i mean by this is some type of OR logic.
so =OR($C:$C={"BASEBALL", "BASE", "BALL", "PIPE", "PIPE FAD"})
But i cannot get it to work.
Please find attached an example spreadsheet.

Cheers,
Julian

[gue2013]

Hi Julian,

may be there is a more elegant way but this works (due to redundancy in your "or" - only look for common substrings)

=OR(IFERROR(IFERROR(SEARCH("BALL";B2);0); IFERROR(SEARCH("PIPE";B2);0))

cheers,
Guenter

[JulianS96]

Hello Guenter, thanks for the response,

Unfortunately i get this error message when attempting to "OK" the formula.
Attachment 656235


I'm sure it can easily be fixed,
Cheers, Julian

EDIT: I FIXED IT, replaced ";" with ",", and added two extra brackets at the end.

[JulianS96]

Please Login or Register  to view this content.

EDIT 2: It only works for "BALL", not "PIPE"

[AliGW]

You didn't replicate it correctly. Try this:

=IFERROR(SEARCH("BALL",B2),IFERROR(SEARCH("PIPE",B2),0))

[JulianS96]

Thanks Ali,
The solution seems so simple but I guess it always is, cheers for that.
On a further note, if i wanted to add another criteria, would I nest IFERROR statements?
E.g. =IFERROR(SEARCH("BALL",B2),IFERROR(SEARCH("PIPE",B2),IFERROR(SEARCH("TREE",B2),IFERROR(SEARCH("HOUSE",B2),0))

Cheers,
Julian

[AliGW]

Yes, but you'll also need to increase the closing brackets on the right - one for each extra criterion.

[JulianS96]

good point, counting it there were 8 "(" and only 6 ")" .
Should have idiot checked it, oh well.
Thanks again!

[AliGW]

No worries.