Conditional Formatting Using an entire Column

[dlopez20]

Hello, 

I have a list of dates on one sheet, for example:

12/2/2023

1/6/2024

1/20/2024

2/19/2024

3/5/2024

and a list of Holidays and other exclusion dates in another sheet:

12/25/2023
1/1/2024
1/15/2024
2/19/2024
5/27/2024
6/19/2024

I'd like to have the first sheet of random dates to be formatted so that if it matches with one of the dates on the second, Holiday/Exclusion sheet, it turns a specific color. 

Is there a way to create a conditional format rule using the entire second sheet Holiday column? I am trying to avoid entering the rules individually for each Holiday/Exclusion date as there are a lot. I have tried A:A or A1:A80 and get the error "Change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5)".

In this case, the rule should format the 2/19/2024 date in the first sheet since it is listed as a holiday in the second sheet.

Thank you.

[#2]

try =ISNUMBER(XMATCH(A1, holidays))

[#3]

@mtarler 

As a comment, ISNUMBER is not required here.

[#4]

@mtarler I tried incorporating that, but it did not work

[#5 · Accepted solution]

@dlopez20 

[#6]

@dlopez20 not sure why. In the attached I give an example show it works and since I included a fun/handy HOLIDAY TABLE I added another version:

this holiday table calcs the holiday for each year you enter under YEAR and 'included' the holiday if indicated in row 1: