Dynamic Named Range based on values in multiple columns

[spence524]

Is there way to create a dynamic named range based on cell values from other columns? The cells in the named range may not be contiguous.
For example, I have a list of holidays or other observed days. I want a dynamic named range of the Day column to reference only those marked as Type: Holiday, and Mark on Calendar = TRUE.
If I remember, there was a combination of OFFSET and perhaps INDEX/MATCH. I've been searching other forums and pages and have not been able to find the solution.

 

[spence524]

Thank you for providing this. Given other needs in my application, it made the most sense to me to use spill formulas (I was trying to avoid it). I used a FILTER with multiple conditions:

FILTER(tbl_Holidays[Date],(tbl_Holidays[Type]="Holiday")*(tbl_Holidays[Mark on Calendar?]=TRUE))