How to pick out multiple values from a range that are in a separate table

Hi Forum,

I have been trying to figure out this problem for a while, I am certain it is possible but I can’t figure it out. I think explaining the problem may be trickier than solving it.

The document is a schedule, and annual leave tracker. In the example I have attached there is 8 weeks worth of the tracker and a table that contains the dates of all the holidays in the year. There are 2 types of holidays represented by yellow and green. The date range I included includes 3 holidays of both types.

What I need to accomplish is a way to determine in each 4 week block (in the actual document there is a whole years worth) if one of the holidays in the table comes up and then be able to tell if the people worked the holiday, or not (represented by PHO) , and what type of holiday it was.

Please let me know if there is a way I can explain this better.

Also if you can suggest a better title for this thread please do, I am not sure exactly what function I am looking for.

Thanks.

Try the attached. I have applied Conditional Formatting to highlight the dates within the work schedules that correspond to the 2 types of holidays.

As for the rest of your request you'll have to be more specific as to how you want it done. Amend your sample file to show how you want the final result to look.

Hi and thanks for the response and the input.. I am sorry for not being specific enough with my first example. The file I have attached this time has the results I am looking for, but I was only able to achieve it by adding rows between the employee's names which wont work in practice because I need to protect and hide them.. But then I cant paste data into the other cells :-(
But at least this works for the example

Do you think this is possible to achieve without using VBA ? I am out of my depth when it comes to code and I need to be able to apply this to a big and complicated sheet and I fear I won’t be able to... I am hoping for a few scalable formulas I can apply on a large scale.

What I am thinking is something I can put to the left of the 4 week block that looks at all the dates in that 4 weeks, compares them with the table of holidays, and if somebody has worked on the holiday count weather it was a DIL holiday or a PTO holiday.

Something to keep in mind, please note how the dates work.. The first date is set and then the rest of the year is relative to it, the sheet needs to work this way as different user will start using it at different times, and in subsequent years the holidays will be on different dates.

I am still finding this difficult to explain accurately, please let me know if there is more details I can fill in. And thanks again.