I am trying to use the Excel "workday" function to assist with handling a large list of public and organisational holidays when scheduling education courses in a multi-state country (Australia). However, the large list of holidays contains locale-specific holidays! So, some holidays apply only to particular States, while others to all States (these are listed out for each state when applicable).
I'm looking for assistance to formulate a way to select/filter the array of holidays to the specific state I'm looking at while maintaining the list in a single table. If using an INDEX-MATCH type input, then I only return a single value. And this doesn't work if there are multiple sequential holidays.
For example, hypothetically, if I try to schedule an Tasmanian event on Easter Monday, the next working day will be the Wed (Tas have Easter Tue as a holiday), while other states could have an event on Tue (as no public holiday).
Is it possible to create a dynamic array in Excel to input to the Workday function?