Array Formula that Looks Up and Returns Values from a Table

Hey all,

I'm creating a workbook that is as automated as possible and part of this involves converting formulas into array formulas to dynamically populate cells with formulas rather than having the user manually drag formulas to extend or manually delete formulas to shrink to the desired range.

The workbook intends to populate cells with the year, month, week, and any hours attributed to a holiday that falls within that specific combination of year, month, and week (a table is populated with this information). I have the first three formulas calculating correctly, but I can't figure out how to convert the last formula into a dynamic array formula using the skills I currently have.

Year:

Formula:

Please Login or Register  to view this content.

Month:

Formula:

Please Login or Register  to view this content.

Week:

Formula:

Please Login or Register  to view this content.

Holiday Hours (Array, not working as intended:

Formula:

Please Login or Register  to view this content.

Holiday Hours (Not an array, working as intended:

Formula:

Please Login or Register  to view this content.

Holidays is a named table within the workbook. Please refer to the attached workbook for the example. Any help would be extremely appreciated!

How about

Formula:

Please Login or Register  to view this content.

You did it again! Thank you so much! I had no idea # could be used in that way. You're an MVP n_n

Glad to help & thanks for the feedback.
The Week formula can also be simplified slightly like

Formula:

Please Login or Register  to view this content.

Thanks so much! This solution is much easier for me to wrap my head around. I was able to update the Month formula using this logic as well.

My pleasure.