# Conditional formatting rule that compares against a range of values

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?

• 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 solution posted might be preferred over one with `ARRAYFORMULA`. Feb 15 '19 at 21:28

• 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))``

• Any way to highlight the whole row (from col A to col E) for the greyed out dates?
– jlo
Feb 15 '19 at 19:47
• @jlo answer updated Feb 15 '19 at 19:55

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`.