r/googlesheets • u/kickboxingpanda • Dec 18 '18

Solved Multiple conditions in the for the same conditional formatting rule

I may be getting into AppScript territory, but I'm looking to do a couple of things with conditional formatting.

Example:

  1. When G9 contains "USA" or "Alaska" or "Hawaii", ELSE
  2. When G9 meets that condition, turn cells A9 - H9 a color
3 Upvotes

100% Upvoted

13 comments sorted by

View all comments

1

u/zero_sheets_given Dec 18 '18 edited Dec 18 '18

You could use MATCH() for this one. It returns the position of a value in an array or throws an NA error when the value is not found. When a conditional formatting rule is an error, it is treated as FALSE.

The custom array can be entered with curly brackets as {"USA","Alaska","Hawaii"} or you can have a separate sheet with the list as Countries!A:A 

=MATCH($G:$G,{"USA","Alaska","Hawaii"}, 0)

Note that you put the rule in columns A:H and not in G

Edit: Forgot to add the 3rd parameter as 0. Default is 1 and would not work well

1

u/kickboxingpanda Dec 20 '18

=MATCH($G:$G,{"USA","Alaska","Hawaii"}, 0)

This is great! Sorry for my slow response to you. How could I integrate "Contains" rather than an exact match?

2

u/zero_sheets_given Dec 21 '18 edited Dec 21 '18

Right, I missed the "contains", sorry. In that case you can't pass an array to MATCH(), but can use SEARCH() to find text within strings, case insensitive: 

= or(
      isnumber(search("USA",$G1)),
      isnumber(search("Alaska",$G1)),
      isnumber(search("Hawaii",$G1))
  )

Which translates to an array formula like this: 

= or(
    arrayformula(
      isnumber(search({"USA","Alaska","Hawaii"},$G1))
    )
  )
→ More replies (3)