Index and Match to exclude names with date parameters

[Asuka]

Hi, I am after a formula which incorporate dates to return an individual's name in the Chair column of table 2.

Ideally, what I'm after is..

1. For the first name from the Employees table to populate into the first cell of the chair column:
a) Where the employee is still with the business (comparing today's date with the end date)
b) If a substitute date is provided also checking this against the date.

2. When it reaches the end of the Employees Name list to loop back to the top.
-> I was able to create a formula which performed the loop but I am struggling with incorporating the date elements. Looking for another way to achieve this
-> =INDEX(Employees_Table[#All], [Employee Name]], MATCH( MOD (ROWS $C$C2:C2), (4), Employees_Table!$A$2:$A$5,0))

I've included an example output table for reference.

Thank you!

Employees Table

Rank	Employee Name	End Date	Current Employee
1	Alan Doe		1
2	John Doe	08/10/2023
3	Margaret Smith		1
4	Zac Brown		1

Table 2:
A B C

Date	Substitute Date	Chair
02/10/2023		=INDEX(Employees_Table[#All], [Employee Name]], MATCH( MOD (ROWS $C$C2:C2), (4), Employees_Table!$A$2:$A$5,0))
16/10/2023
30/10/2023	06/11/2023
13/11/2023
27/11/2023
11/12/2023

Example Output:

Date	Substitute Date	Chair
02/10/2023		Alan Doe
16/10/2023		Margaret Smith
30/10/2023	06/11/2023	Zac Brown
13/11/2023		Alan Doe
27/11/2023		Margaret Smith
11/12/2023		Zac Brown