Excel Formula Help!!! Combining IFERROR, INDEX, MATCH & SUMPRODUCT

[Bstatum]

Hello excel community.

I needing some formula help. I am working on a class scheduling project that uses the Excel Student Schedule template to read specific cell data from a master list and then paste that data into the corresponding worksheet.

I have set up my worksheet similar to how the Excel template works.

My formula combines an IFERROR, INDEX, MATCH & SUMPRODUCT function, but unlike the Excel template I am wanting to paste information across multiple worksheets and not just a single sheet.

Current function : =IFERROR(INDEX(ClassList,MATCH(SUMPRODUCT((ClassList[ROOM]=MondayDanksHeaders[[#Headers],[MAIN AUD]])*(ROUNDDOWN($B5,10)>=ROUNDDOWN(ClassList[START TIME],10))*($B5<=ClassList[END TIME]),ClassList[UNIQUE]),ClassList[UNIQUE],0),2),0)

I am having difficulty working out how get the function to recognize the specific worksheet to paste the data into. At the moment any data that matches gets pasted in the “Monday Danks” sheet, but I need the formula to also read the ‘Class List’ ‘Day’ column that matches the ‘Class Schedule’ sheets.

If there is would be a better way to run the worksheet (ex. Through VBA). I would appreciate the help.

Please see my samples below. My apologies if the samples arent great. This forum is quiet difficult to post on!

“Master Data List” – Row 11 as example

CLASS LIST
TYPE	SUBJECT	YEAR	STREAM	SEMESTER	CODE	DAY	LOCATION	ROOM	START TIME	END TIME	UNIQUE
Core	Leading a Church Department	Advanced	Pastoral		CA1P1	Thursday Danks	Danks				1
Tutorial	Corinthian Correspondence	Advanced	Pastoral		Tutorial 1	Friday Danks	Danks	ARK			2
Tutorial	Corinthian Correspondence	Advanced	Pastoral		Tutorial 2	Friday Danks	Danks	ARK			3
Core	Leading a Church Department	Advanced	Pastoral		CA2P1	Wednesday Danks	Danks	LIBRARY 2			4
Core	Corinthian Correspondence	Advanced	Pastoral		CA2P2						5
Tutorial	Corinthian Correspondence	Advanced	Pastoral		Tutorial 3	Friday Danks	Danks	ARK			6
Tutorial	Preaching	Advanced	Pastoral		Tutorial 1	Wednesday Danks	Danks	ARK			7
Tutorial	Training Skills	Advanced	Pastoral		Tutorial 3	Wednesday Danks	Danks	ARK			8
Core	Communication in Ministry	Certificate	Pastoral		CC2P3	Monday Danks	Danks	ARK	10:15 AM	11:15 AM	9
Lecture	Preaching	Advanced	Pastoral	N/A		Friday Doody	Doody	MAIN AUD			10
Core	Personal Leadership	Certificate	Pastoral		CC2P3	Monday Danks	Danks	ARK	11:15 AM	12:45 PM	11
Tutorial	Bible Study Methods	Certificate	Pastoral		CC1P2	Wednesday Danks	Danks	ARK			12

<colgroup><col><col><col span="4"><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

First “Day” Sheet example titled “Monday Danks” – Row 13 to follow example above

CLASS SCHEDULE	SCHEDULE START	TIME INTERVAL	Class List <tbody> </tbody>
8:00 AM	15 MIN
TIME	MAIN AUD	CUBBYHOUSE	ZOO	PARENTS	VOLTAGE	ARK	2ND AUD	MUSIC	LIBRARY 1	LIBRARY 2
8:00 AM								0	0	0
8:15 AM								0	0	0
8:30 AM								0	0	0
8:45 AM								0	0	0
9:00 AM					Communication in Ministry			0	Music Theory for Vocals	0
9:15 AM					Communication in Ministry			0	Music Theory for Vocals	Communication in Ministry
9:30 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Communication in Ministry		Songwriting Workshop 2	0	Music Theory for Vocals	Communication in Ministry
9:45 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Communication in Ministry		Songwriting Workshop 2	0	Music Theory for Vocals	Communication in Ministry
10:00 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop			Songwriting Workshop 2	0	Music Theory for Vocals	Communication in Ministry
10:15 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Personal Leadership	Communication in Ministry	Songwriting Workshop 2	0	Public Speaking & Presentation	Communication in Ministry
10:30 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Personal Leadership	Communication in Ministry	Songwriting Workshop 2	0	Public Speaking & Presentation	0
10:45 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Personal Leadership	Communication in Ministry	Songwriting Workshop 2	0	Public Speaking & Presentation	0
11:00 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Personal Leadership	Communication in Ministry	Songwriting Workshop 2	0	Public Speaking & Presentation	0
11:15 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Personal Leadership	Leading a Church Department	Songwriting Workshop 2	0	Public Speaking & Presentation	Personal Leadership
11:30 AM	Musicians Workshop	Vocal Workshop	Musicians Workshop	Vocal Workshop	Personal Leadership	Personal Leadership	Songwriting Workshop 2	0	Public Speaking & Presentation	Personal Leadership

<colgroup><col><col span="6"><col><col span="3"></colgroup><tbody>
</tbody>

If you’re interested in helping I can send the full project for a better understanding.
Thank you

 

[Bstatum]

To clarify the my second example on the excel it only posts the Subject name in the first time cell (ex. Communication in Ministry" Subject starting at 10:15am and ending 11:15am. but only posting the name in the 10:15 row.