Conditional formatting rule that compares against a range of values

Question

I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample spreadsheet

So far I have this custom formula =$A:$A=$G$2:$G$20

...which only marks the first instance of the date range. Is there any way I can solve this without having to create a separate rule for each element of col G?

Answer 1

• custom formula:

  =ARRAYFORMULA(IF(NOT(ISNA(VLOOKUP(A2:A, G$2:G, 1, 0))), 1))

  

• for row highlight (A:E) try:

  =ARRAYFORMULA(IF(NOT(ISNA(VLOOKUP($A2:A,$G$2:G,1,0))),1))

  

Answer 2

If you have your data in, say, A2:A200 (assuming a header row), and you holidays are stored in G2:G20, then set a Conditional Formatting rule with:

Apply to range: A2:A200

Custom formula is: =SUMPRODUCT((A2=($G$2:$G$20)))>=1

You could also use Apply to range: A2:E to also color columns B:E and to not limit the number of rows.

In Excel, using non-array formulas provides some advantages over array formulas, whenever the same result can be obtained. I am not sure the same applies for Goggle Sheets, but given that possibility the present solution might be preferred over one with ARRAYFORMULA.