1

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. – sancho.s Feb 15 at 21:28
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))

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.