Nested Formula... IF MATCH AND INDEX ?

[asiram_m]

I am not sure if this is possible...

I am trying to see if there is a formula that will work, or a modification that can be made to my spreadsheet, to have Excel easily update a calendar (picture: Schedule Overview) based on data in a different tab (picture: Schedule Details). The end goal is that it would pull in the correct school name that is scheduled on that floor, during the correct AM/PM time on the specific date based on notes from the Schedule Details tab.

I created some (hidden) rows to assemble the date represented by the cells, as I was not sure how else to go about this. My thought process was I would need IF, INDEX or MATCH and AND formulas to accomplish this. I assume this would be entered in as an array formula? If this is possible... My hangup us on the Schedule Details - my weekdays are not headers.

This is where I started out, but I am struggling. Any help or insight is appreciated.

 

[PeteWright]

Hi, I'm not sure what exactly you need.

Could you create a result Sheet (no formulas, just the values that should appear) with the data it should contain?

 

[asiram_m]

Hi, I'm not sure what exactly you need.

Could you create a result Sheet (no formulas, just the values that should appear) with the data it should contain?

Hello,

So the idea is that on Example 1, I would fill in data for each group. I would add in their start date on campus as well as their end date and then the floor they will be on and their schedule (i.e. Tuesday PM sessions) and then the end goal is on Example 2, it is a visual overview of who is here and when and where. (There will never be an overlap as only one group can be scheduled per shift and the schedules are consistent throughout their duration.) Ideally it will match the date in the column to the date range of the schools and look for a match and then from there the locations should match, and the AM/PM time slots should match and if there is a match, I would like for it to pull in the school name. Then I can use conditional formatting to color code by school.