Index Match - retrieve a value between 2 dates across different rows

JennyHoA20181 

=IFERROR(INDEX('Application History 2023.2'!$D$2:$D$8589,LARGE(IF(('Application History 2023.2'!$A$2:$A$8589=B2)*('Application History 2023.2'!$E$2:$E$8589<=$V$1),ROW('Application History 2023.2'!$G$2:$G$8589)-1),1)),INDEX('Application History 2023.2'!$C$2:$C$8589,MATCH(B2,'Application History 2023.2'!$A$2:$A$8589,0)))

You are right! The database in the Application History 2023.2 sheet only needs to be sorted by the Edit Date column from oldest to newest.

The name of the sheet is now Application History 2023.2 therefore the formula must refer to this sheet.

After some columns were deleted the formula must be adjusted for example:

('Application History 2023.2'!$E$2:$E$8589<=$V$1)

because the date is now in $V$1 before it was in cell $Z$1. Now the dates are in column E and before they were in column G in the Application History 2023.2 sheet.

The formula must be entered as an arrayformula not as an array. Try to enter the formula in cell S2 of the "Rel Week CI+ 20th May 2023.2" sheet and confirm it with ctrl+shift+enter. Then fill the formula down.