Index, Match and Rank

[rex759]

Hello
What I am attempting to do is have Index and Match pull the time and class and sort by earliest time for that day. I thought I could rank the times first to show the earliest time and then index and match to that column. It worked for the first date of 7/1. When I changed the date to 7/2, the first entry is showing the information from 7/1. It should show a time of 8:10 and Class E1.

Second question, if it does work, I was wondering if I can combine the two formulas in column I.
Any help is appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Time	Class		7/1/2020	Rank	Time	Class	Combined
2	7/1/2020	2:00 PM	E3			3	10:00 AM	E8	10:00 AM
3	7/1/2020	1:30 PM	E5			2	1:30 PM	E5	1:30 PM
4	7/2/2020	8:10 AM	E1			0	2:00 PM	E3	2:00 PM
5	7/1/2020	10:00 AM	E8			1	#N/A	#N/A
6	7/2/2020	4:00 PM	b1			3	#N/A	#N/A
7	7/3/2020	12:00 PM	b2			1	#N/A	#N/A
8	7/5/2020	11:30 AM	b3			1	#N/A	#N/A
9
10	Date	Time	Class		7/2/2020	Rank	Time	Class	Combined
11	7/1/2020	2:00 PM	E3			1	2:00 PM	E3	2:00 PM
12	7/1/2020	1:30 PM	E5			1	4:00 PM	b1	4:00 PM
13	7/2/2020	8:10 AM	E1			1	#N/A	#N/A	#N/A
14	7/1/2020	10:00 AM	E8			1	#N/A	#N/A
15	7/2/2020	4:00 PM	b1			2	#N/A	#N/A
16	7/3/2020	12:00 PM	b2			1	#N/A	#N/A
Sheet2

Cell Formulas
Range	Formula
F2:F4, F5:F8	F2	=COUNTIFS($B$2:$B$8,"<="&B2,$A$2:$A$8,"="&$E$1)
G2:G4, G5:G8	G2	=INDEX($B$2:$B$8,MATCH(ROWS(G$2:$G2),$F$2:$F$8,0))
H2:H4, H14:H16, H11:H13, H5:H8	H2	=INDEX($C$2:$C$8,MATCH(G2,$B$2:$B$8,0),MATCH($E$1,$A$2:$A$8,0))
I2:I4	I2	=COUNTIFS($B$2:$B$8,"<="&B2,$A$2:$A$8,"="&$E$1)+INDEX($B$2:$B$8,MATCH(ROWS(G$2:$G2),$F$2:$F$8,0))
F11:F13, F14:F16	F11	=COUNTIFS($B$11:$B$17,"<="&B11,$A$11:$A$17,"="&$E$10)
G11:G13, G14:G16	G11	=INDEX($B$11:$B$17,MATCH(ROWS(G$11:$G11),$F$11:$F$17,0))
I11:I13	I11	=COUNTIFS($B$11:$B$17,"<="&B11,$A$11:$A$17,"="&$E$10)+INDEX($B$11:$B$17,MATCH(ROWS(G$11:$G11),$F$11:$F$17,0))

 

[Toadstool]

Hi Rex759,

Let me come at this a different way. Does this do what you want?

Rex759.xlsx
	A	B	C	D	E	F	G
1	Date	Time	Class		7/1/2020	Time	Class
2	7/1/2020	2:00 PM	E3			10:00 AM	E8
3	7/1/2020	1:30 PM	E5			1:30 PM	E5
4	7/2/2020	8:10 AM	E1			1:30 PM	b2
5	7/1/2020	10:00 AM	E8			2:00 PM	E3
6	7/2/2020	4:00 PM	b1
7	7/1/2020	1:30 PM	b2
8	7/5/2020	11:30 AM	b3
9
10	Date	Time	Class		7/2/2020	Time	Class
11	7/1/2020	2:00 PM	E3			8:10 AM	E1
12	7/1/2020	1:30 PM	E5			4:00 PM	b1
13	7/2/2020	8:10 AM	E1
14	7/1/2020	10:00 AM	E8
15	7/2/2020	4:00 PM	b1
16	7/3/2020	12:00 PM	b2
Sheet1 (2)

Cell Formulas
Range	Formula
F2:F8	F2	=IF(COUNTIF($A$2:$A$8,$E$1)>=(ROW()-ROW($F$1)),AGGREGATE(15,6,($B$2:$B$8)/($A$2:$A$8=$E$1),ROW()-ROW($F$1)),"")
G2:G8	G2	=IF(F2="","",INDEX($C$2:$C$8,AGGREGATE(15,6,ROW($A$2:$A$8)-ROW($A$1)/(($A$2:$A$8=$E$1)*($B$2:$B$8=F2)),COUNTIF($F$2:$F2,F2))))
F11:F16	F11	=IF(COUNTIF($A$11:$A$16,$E$10)>=(ROW()-ROW($F$10)),AGGREGATE(15,6,($B$11:$B$16)/($A$11:$A$16=$E$10),ROW()-ROW($F$10)),"")
G11:G16	G11	=IF(F11="","",INDEX($C$11:$C$16,AGGREGATE(15,6,ROW($A$11:$A$16)-ROW($A$10)/(($A$11:$A$16=$E$10)*($B$11:$B$16=F11)),COUNTIF($F$11:$F11,F11))))

 

[rex759]

That works! Thank you very much