Highlight cells based on public holidays for different states

[zaidk4496]

Greetings everyone, 

I'm trying to highlight cells in columns A:H if they fall on public holiday dates mentioned in the cells J2:L18. Now what if the public holidays differ according to different states? Ex: If we highlight the columns based on state – VIC’s public holidays, and if state NSW’s date fall on the same date but it is not a public holiday, how do we highlight cells based on different state’s holidays without disturbing other states' normal days?

Please refer to the formula I have used. 

Thanks in a

[#2 · Accepted solution]

@zaidk4496 I would start by assigning a name to each range of state holidays, using the abbreviations used in column B.

Then the following rule, applied to columns A:H will highlight the row if the date in A is a holiday in state B:

=SUM(IFERROR(FIND($A2,INDIRECT($B2)),0))

Please find a working example attached.

[#3]

@Riny_van_Eekelen Got it. 

You're a legend. 

Thanks