Sumproduct or Index Match Function using Multiple Criteria in another worksheet

[lai_arceo]

Good day. I hope you are all doing safe during this time.

I really am not sure which function I should use to look up for values that I need in my worksheet.

I have a worksheet (BASE) containing the summary of schedules of all the teachers with their classes, date, day, time and language.

WORKING FILE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	WEEK NO.	22
2	INCLUSIVE DATES:	MAY 25 - 30, 2021
3	TEACHER	CLASS	DATE	DAY	TIME	LANGUAGE	CLASS	DATE	DAY	TIME	LANGUAGE	CLASS	DATE	DAY	TIME	LANGUAGE
4	MICHAEL SAN JUAN	DOHA	20-May-21	THURS	7:45 PM	TAGALOG	MESAIEED	21-May-21	FRI	6:45 AM	TAGALOG	AMMAN	21-May-21	SUN	11:00 AM	TAGALOG
5	ALICE DE GUZMAN	DUKHAN	19-May-21	WEDS	7:45 PM	ENGLISH	DOHA	22-May-21	SAT	7:45 PM	ENGLISH	DOHA	19-May-21	WEDS	7:45 AM	ENGLISH
6
7		SCHEDULE 1					SCHEDULE 2					SCHEDULE 3
BASE

I have another worksheet (SUM) that I need to summarize everyone's schedule and see them all at a glance. I need to find (1) THE TEACHER and (2) THE DATE based on the class, day, time and language written.

I have used SUMPRODUCT to no avail. Appreciate if someone could assist in this.

WORKING FILE.xlsx
	A	B	C	D	E	F	G
1	SUMMARY OF CLASS SCHEDULE
2
3
4	WEEK NO.:			INCLUSIVE DATES:
5
6	SUMMARY
7	CLASS	DAY	TIME	LANGUAGE	DATE	TEACHER	SIGNATURE
8	DOHA	THURS	7:45 PM	TAGALOG		#VALUE!
9	DOHA	WEDS	7:45 PM	TAGALOG
10	DUKHAN	WEDS	7:45 AM	ENGLISH
11	MESAIEED	FRI	9:00 PM	TAGALOG
12	AMMAN	SAT	11:00 AM	TAGALOG
SUM

Cell Formulas
Range	Formula
F8	F8	=SUMPRODUCT(($A8:BASE!$B4=BASE!$B17)*($A8:BASE!G4=BASE!G17)*($A8:BASE!L4=BASE!L17)*($A8:BASE!Q4=BASE!Q17)*($A8:BASE!V4=BASE!V17)*($B8:BASE!$D4=BASE!$D17)*($B8:BASE!I4=BASE!I17)*($B8:BASE!N4=BASE!N17)*($B8:BASE!S4=BASE!S17)*($B8:BASE!X4=BASE!X17)*($C8:BASE!$B4=BASE!$B17)*($C8:BASE!J4=BASE!J17)*($C8:BASE!O4=BASE!O17)*($C8:BASE!T4=BASE!T17)*($C8:BASE!Y4=BASE!Y17)*($D8:BASE!$F4=BASE!$F17)*($D8:BASE!K4=BASE!K17)*($D8:BASE!P4=BASE!P17)*($D8:BASE!U4=BASE!U17)*($D8:BASE!Z4=BASE!Z17),BASE!A4:BASE!A17)

Thank you so much and keep safe.

 

[Snakehips]

@lai_arceo Questions rather than answer atm I'm afraid.
For me, that data layout in Base does not make this a real proposition foe formulas.

Might it be possible for you to insert an extra column to the right of each schedule where you could have a unique reference for each class session?
Also, I'm confused that none of the dates in Base example are within the week number dates?

I'm out for a couple of hours now so here is a very rough example of how that might simplify things.

MRXLMAY21 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	WEEK NO.	22
2	INCLUSIVE DATES:	MAY 25 - 30, 2021
3	TEACHER	CLASS	DATE	DAY	TIME	LANGUAGE	Ref	CLASS	DATE	DAY	TIME	LANGUAGE
4	MICHAEL SAN JUAN	DOHA	20-May-21	THURS	0.822917	TAGALOG	x1	MESAIEED	21-May-21	FRI	0.28125	TAGALOG	abc999
5	ALICE DE GUZMAN	DUKHAN	19-May-21	WEDS	0.822917	ENGLISH	abc123	DOHA	22-May-21	SAT	0.822917	ENGLISH	www
6
7		SCHEDULE 1						SCHEDULE 2
8
9
10
11
12						Example
13				abc999		MICHAEL SAN JUAN
14
Base

Cell Formulas
Range	Formula
F13	F13	=INDEX($A$1:$A$11,SUMPRODUCT((D13=$A$1:$S$11)*ROW($A$1:$S$11)))

 

[lai_arceo]

@Snakehips Thank you for the suggestion. It's much appreciated. I'll try referencing each class schedule and let you know how that turns out.

 

[lai_arceo]

@Snakehips Your suggestion and formula worked great! Thank you!

WORKING FILE.xlsx
	A	B	C	D	E	F	G	H
1	SUMMARY OF CLASS SCHEDULE
2
3
4	WEEK NO.:			INCLUSIVE DATES:
5
6	SUMMARY
7	CLASS	DAY	TIME	LANGUAGE	DATE	TEACHER	SIGNATURE	REF
8	DOHA	THURS	7:45 PM	TAGALOG		MICHAEL SAN JUAN		S1.1
9	DOHA	WEDS	7:45 PM	TAGALOG		ALICE DE GUZMAN		S1.2
10	DUKHAN	WEDS	7:45 AM	ENGLISH				S1.3
11	MESAIEED	FRI	9:00 PM	TAGALOG				S1.4
12	AMMAN	SAT	11:00 AM	TAGALOG				S1.5
SUM

Cell Formulas
Range	Formula
F8:F9	F8	=INDEX(BASE!$A$1:$A$11,SUMPRODUCT((H8=BASE!$A$1:$S$11)*ROW(BASE!$A$1:$S$11)))

However, I am not sure how to use the references for the class schedule now to look up the corresponding date. May I ask for assistance on this last piece of the puzzle again?

 

[lai_arceo]

@Snakehips I was able to figure how to get the corresponding date. Thank you for the initial suggestion!

 

[Snakehips]

@lai_arceo I'm so pleased it helped.
Try this for dates.

MRXLMAY21 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	SUMMARY OF CLASS SCHEDULE
2
3
4	WEEK NO.:			INCLUSIVE DATES:
5
6	SUMMARY
7	CLASS	DAY	TIME	LANGUAGE	DATE	TEACHER	SIGNATURE	Ref
8	DOHA	THURS	7:45 pm	TAGALOG	20/05/2021	MICHAEL SAN JUAN		S1.1
9	DOHA	WEDS	7:45 pm	TAGALOG
10	DUKHAN	WEDS	7:45 am	ENGLISH	19/05/2021	ALICE DE GUZMAN		S1.3
11	MESAIEED	FRI	9:00 pm	TAGALOG
12	AMMAN	SAT	11:00 am	TAGALOG
13
Sum

Cell Formulas
Range	Formula
E8:E12	E8	=IFERROR(INDEX(Base!$A$1:$S$11,SUMPRODUCT((H8=Base!$A$1:$S$11)*ROW(Base!$A$1:$S$11)),SUMPRODUCT((H8=Base!$A$1:$S$11)*COLUMN(Base!$A$1:$S$11))-4),"")
F8:F12	F8	=IFERROR(INDEX(Base!$A$1:$A$11,SUMPRODUCT((H8=Base!$A$1:$S$11)*ROW(Base!$A$1:$S$11))),"")

 

[lai_arceo]

@lai_arceo I'm so pleased it helped.
Try this for dates.

MRXLMAY21 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	SUMMARY OF CLASS SCHEDULE
2
3
4	WEEK NO.:			INCLUSIVE DATES:
5
6	SUMMARY
7	CLASS	DAY	TIME	LANGUAGE	DATE	TEACHER	SIGNATURE	Ref
8	DOHA	THURS	7:45 pm	TAGALOG	20/05/2021	MICHAEL SAN JUAN		S1.1
9	DOHA	WEDS	7:45 pm	TAGALOG
10	DUKHAN	WEDS	7:45 am	ENGLISH	19/05/2021	ALICE DE GUZMAN		S1.3
11	MESAIEED	FRI	9:00 pm	TAGALOG
12	AMMAN	SAT	11:00 am	TAGALOG
13
Sum

Cell Formulas
Range	Formula
E8:E12	E8	=IFERROR(INDEX(Base!$A$1:$S$11,SUMPRODUCT((H8=Base!$A$1:$S$11)*ROW(Base!$A$1:$S$11)),SUMPRODUCT((H8=Base!$A$1:$S$11)*COLUMN(Base!$A$1:$S$11))-4),"")
F8:F12	F8	=IFERROR(INDEX(Base!$A$1:$A$11,SUMPRODUCT((H8=Base!$A$1:$S$11)*ROW(Base!$A$1:$S$11))),"")

I really appreciate the assistance! Both of the formula work great! Thank you, thank you, thank you!