Help with formula to return latest date balance

[Sean15]

Hi:
Formua in G15 is an attempt to return the latest balance from column E.
Required value should be $2,508.50 since latest date is 11/17/23.
Could you show me the proper way to do this?

Thank you.
S

Desert Fin 2023.xlsx
	A	B	C	D	E	F	G
1	Posting Date	Transaction Type	Amount	Type	Balance
2	11/17/23	Credit	500.00	Deposit	2,508.50
3	11/16/23	Debit	(2.61)	Withdrawal	2,008.50
4	11/16/23	Debit	(3.55)	Withdrawal	2,011.11
5	11/16/23	Debit	(5.63)	Withdrawal	2,014.66
6	10/25/23	Debit	(22.65)	Withdrawal	2,020.29
7	10/17/23	Debit	(8.67)	Withdrawal	2,042.94
8	10/15/23	Debit	(9.98)	Withdrawal	2,051.61
9	09/30/23	Debit	(11.25)	Withdrawal	2,061.59
10	09/26/23	Debit	(14.66)	Withdrawal	2,072.84
11	08/25/23	Credit	200.00	Deposit	2,087.50
12
13
14
15							2087.50
Sheet1 (2)

Cell Formulas
Range	Formula
E2:E10	E2	=E3+C2
G15	G15	=IFERROR(LOOKUP(9.99999999999999E+307,($A$2:$A$11),$E$2:$E$11),"")

 

[DanteAmor]

Try:

=LOOKUP(2,1/(MAX(A2:A11)),E2:E11)

 

[Sean15]

Thank you. Testing formula in post #2.
If I change date in A2 to 10/15/23, formula returns $2,508.50
Required value should be $2,008.50.
Could you advise please?

Desert Fin 2023.xlsx
	A	B	C	D	E	F	G
1	Posting Date	Transaction Type	Amount	Type	Balance
2	10/15/23	Credit	500.00	Deposit	2,508.50
3	11/16/23	Debit	(2.61)	Withdrawal	2,008.50
4	11/16/23	Debit	(3.55)	Withdrawal	2,011.11
5	11/16/23	Debit	(5.63)	Withdrawal	2,014.66
6	10/25/23	Debit	(22.65)	Withdrawal	2,020.29
7	10/17/23	Debit	(8.67)	Withdrawal	2,042.94
8	10/15/23	Debit	(9.98)	Withdrawal	2,051.61
9	09/30/23	Debit	(11.25)	Withdrawal	2,061.59
10	09/26/23	Debit	(14.66)	Withdrawal	2,072.84
11	08/25/23	Credit	200.00	Deposit	2,087.50
12
13
14
15							2508.50
Sheet1 (2)

Cell Formulas
Range	Formula
E2:E10	E2	=E3+C2
G15	G15	=LOOKUP(2,1/(MAX(A2:A11)),E2:E11)

 

[Scott Huish]

Shouldn't it be 2014.66. The last value for the largest date?
If so,
=LOOKUP(2,1/(A2:A11=(MAX(A2:A11))),E2:E11)

 

[Phuoc]

Like this?

=VLOOKUP(MAX(A2:A11),A2:E11,5,0)

 

[Sean15]

@Phuoc, thank you. Clever use of VLOOKUP since it will will only find the first match if there is more than one matching value in Column A.

 

[Alex Blakenburg]

@sean, you are mixing and matching though. Your additional test case with an older date (10/15/23) at the top suggest the dates are not in order but by taking the first occurence if there are mutliple transactions on the same date assumes that they are in descending order.