If multiple criteria are met in sheet 1 then paste in Sheet 2 if the dates coincide

Hello everyone,

I have started playing around with calendars in excel and i've ran into a wall.
Basically i have created a generic shift pattern spanning for 1 year in Sheet 1, and in Sheet 2 I am trying to create an interactive Attendance calendar. I was able to create a working 30-31 days calendar that can be scrolled through the years and the months. I Was also able to get the code to show me any change (Lieu days) that occurs in the month of march in regards to one of the employees, however this stops working if i change to the next month.

Is it possible to add a formula where it scans the complete year in Sheet 1(Row 6) then if it matches the 30-31 days calendar pattern in Sheet 2(row 9) then it will return a copy of whatever is written in Sheet 1(Row B7....B18 ,coinciding to the specific date) in Sheet 2 Cell G11:AK11 ; G12:AK12 ETC.
The formula so far:

=IF(AND(Shift!$B$7:$B$7="Jerome",Shift!C6=Attendance!K9,Shift!C7="Lieu"),"Lieu","p")

I have tried to also add vlookup and INDEX, however it doesn't seem to properly work.

There is an attached example below.

Any insight will be greatly appreciated.

Thank you!