Find a Meeting Time

[daveasu]

Hello All,
I'm attempting to create a spreadsheet that will visually show the days/times that instructors are busy to find an unused time to schedule a meeting.
I have a schedule of Names, Begin & End times, and days of the week.

Find Meeting Time.xlsx
	J	K	L	M	N	O	P	Q
3	NAME	BEGIN_TIME	END_TIME	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY
4	Smith	1050	1140	M
5	Smith	925	1040		T		R
6	Smith	1050	1205		T		R
7	Jones	931	1101	M	T	W	R	F
8	Thomas	1050	1255		T		R
9	Thomas	925	1040		T		R
10	Thomas	1340	1430		T		R
11	Thomas	1440	1555				R
12	Thomas	1610	1700				R
13	Jones	800	930	M	T	W	R	F
14	Jones	1130	1245		T		R
15	Jones	1300	1555		T		R
16	Jones	1130	1400	M		W
Class Schedule

I have a formula that is close to finding the start times, but I'm stuck at finding if a time is within the range of the "busy" time of the instructor?
It doesn't matter if all the instructors who are busy are listed in the cell. Just one busy instructor would cancel that time slot.
In the example, I'm trying to get "Jones" to show up in the M column in the cells 815 - 1045 (although Smith could be in the 1045)

Find Meeting Time.xlsx
	A	B	C	D	E	F	G
1
2		Time	M	T	W	R	F
3		800	Jones	Jones	Jones	Jones	Jones
4		815
5		830
6		845
7		900
8		915		Smith		Smith
9		930	Jones	Jones	Jones	Jones	Jones
10		945
11		1000
12		1015
13		1030
14		1045	Smith	Smith		Smith
15		1100
16		1115	Jones	Jones	Jones	Jones
17		1130	Jones	Jones	Jones	Jones
18		1145
19		1200
20		1215
21		1230
22		1245		Jones		Jones
23		1300		Jones		Jones
24		1315
25		1330		Thomas		Thomas
26		1345
27		1400
28		1415
29		1430				Thomas
30		1445
31		1500
32		1515
33		1530
34		1545
35		1600				Thomas
36		1615
37		1630
38		1645
39
Class Schedule

Cell Formulas
Range	Formula
C3:G38	C3	=XLOOKUP(1,(M:M=C$2)*($K:$K>=$B3)*($K:$K<=$B4),$J:$J,"")

Named Ranges
Name	Refers To	Cells
'Class Schedule'!_FilterDatabase	='Class Schedule'!$J$3:$Q$17	C3:G38

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1
2		Time	M	T	W	R	F
3		800	Jones	Jones	Jones	Jones	Jones
4		815	Jones	Jones	Jones	Jones	Jones
5		830	Jones	Jones	Jones	Jones	Jones
6		845	Jones	Jones	Jones	Jones	Jones
7		900	Jones	Jones	Jones	Jones	Jones
8		915	Jones	Jones	Jones	Jones	Jones
9		930	Jones	Smith	Jones	Smith	Jones
10		945	Jones	Smith	Jones	Smith	Jones
11		1000	Jones	Smith	Jones	Smith	Jones
12		1015	Jones	Smith	Jones	Smith	Jones
13		1030	Jones	Smith	Jones	Smith	Jones
14		1045	Jones	Jones	Jones	Jones	Jones
15		1100	Smith	Smith	Jones	Smith	Jones
16		1115	Smith	Smith		Smith
17		1130	Smith	Smith	Jones	Smith
18		1145	Jones	Smith	Jones	Smith
19		1200	Jones	Smith	Jones	Smith
20		1215	Jones	Thomas	Jones	Thomas
21		1230	Jones	Thomas	Jones	Thomas
22		1245	Jones	Thomas	Jones	Thomas
23		1300	Jones	Jones	Jones	Jones
24		1315	Jones	Jones	Jones	Jones
25		1330	Jones	Jones	Jones	Jones
26		1345	Jones	Thomas	Jones	Thomas
27		1400	Jones	Thomas	Jones	Thomas
28		1415		Thomas		Thomas
29		1430		Thomas		Thomas
30		1445		Jones		Thomas
31		1500		Jones		Thomas
32		1515		Jones		Thomas
33		1530		Jones		Thomas
34		1545		Jones		Thomas
35		1600
36		1615				Thomas
37		1630				Thomas
38		1645				Thomas
Data

Cell Formulas
Range	Formula
C3:G38	C3	=TAKE(FILTER($J$4:$J$16,($K$4:$K$16<=$B3)*($L$4:$L$16>=$B3)*(M$4:M$16<>""),""),1)

 

[HongRu]

try it.
C3
=TEXTJOIN(",",TRUE,UNIQUE(FILTER($J$4:$J$16,(M$4:M$16<>"")*(($K$4:$K$16<=$B3)*($L$4:$L$16>=$B3)+($K$4:$K$16>=$B3)*($K$4:$K$16<$B4)),"")))

 

[daveasu]

Thank you Fluff & HongRu!! I was no where near your perfect solution! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.