Hi There,
Hopefully this should help get this started off at least, if not entirely point you in the right direction.
The layout is a tad tricky for lookups as you have duplications in Column A, Lesson, Location etc, you also have duplications in the date row, and partial text match for the staff member name as A. Collar/L. Chen for example.
Without altering the layout, the best thing I've come up with is this.....
On the Indiv Sched Sheet....
These must all be entered as an Array Formula. To do this, hold down left control and left shift together, while pressing enter, the formula should have {} around it in the input box when done correctly.
In B5: (Copy and paste to table section B5:H9)
In B11: (Copy and paste to table section B11:H15)
In B17: (Copy and paste to table section B17:H21)
You would need to continue to follow this same logic all the way down the sheet, modifying the relevant range sections in the formulas as you go down. It's done in the morning/afternoon/evening sections each separately, to be able to tell the difference between location, lesson, instructor etc for each section. A match on column A otherwise will find multiple instances of all of these, this is not the only way around that problem, but it's one way of doing it.
I attached your example with this in place for week 1, hope it helps.
Regards,
Bookmarks