excel - Return latest balance between two dates

Question

I'm looking for a formula that could give me the latest balance between two dates. I have one cell for the starting date and cell one for the end date.I also have some duplicate dates for each accounts in my file. This is the formula that I have right now in my file that is working well but now I need to put the date range in the formula: =LOOKUP(10^35;$F$15:$F$68/($D$15:$D$68=$E8))

To translate the formula that I have in the file vs the example below, it would be something like this:

=LOOKUP(10^35;$C$2:$C$4/($D$2:$D$5=$F4))

	A	B	C	D	E	F
1	DATE	AMOUNT	BALANCE	ACCOUNT	START DATE:	Jan-1-2020
2	Jan-5-2020	50$	50$	ABC...	END DATE:	Dec-31-2020
3	Dec-30-2020	60$	110$	ABC...
4	Dec-30-2020	40$	150$	ABC...		ABC...
5	Dec-31-2020	100$	100$	DEF...		DEF...

question from:https://stackoverflow.com/questions/66055333/return-latest-balance-between-two-dates

Answer 1

Use INDEX/AGGREGATE:

=INDEX(C:C,AGGREGATE(14,7,ROW($C$2:$C$5)/(($A$2:$A$5>=$F$1)*($A$2:$A$5<=$F$2)*($D$2:$D$5=F4)),1))