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)) |