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!

Try

in G11

=IFERROR(INDEX(Shift!$C$7:$NC$19,MATCH($F11,Shift!$B$7:$B$19,0),MATCH(G$9,Shift!$C$6:$NC$6,0)),"")

Copy across and down

You can hide 0s by formatting as 0;;;@

That worked like a charm, thank you for your time, I will definitely play around with what you've given me and explore additional applications!

You are very welcome.

The formula I provided is a very frequently used one to extract data in "2 dimensions" (row and column) so it is worth understanding how it works.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.