Formula to find last date of a given MONTH and return value from another cell on the same row

[UpToTheGills]

Been trying without success to nail this one.

I have a summary page on one worksheet where I want to calculate the percentage gain (or loss) for an account against the previous month (cell H6), where the data is in a table on another worksheet.

IC Live 260051267 - Trading Journal.xlsm
	C	D	E	F	G	H
3	Month / Year		Gain on A/c
4
5		Aug 2023		$1,393.37		46.4%
6		Sep 2023		$1,551.60		297000.0%
Stats Overview

Cell Formulas
Range	Formula
F5:F6	F5	=IF(TODAY()<D5,"",SUMIFS(ENTRIES[Net P&L (£)],ENTRIES[Broker Exit Time],">="&D5,ENTRIES[Broker Exit Time],"<"&EDATE(D5,1)))
H5	H5	=F5/3000
H6	H6	=VLOOKUP(MAX(EOMONTH(D5,0)),ENTRIES[[Broker Exit Time]:[A/c Balance]],22,TRUE)

and I just cannot figure out the correct syntax to make it work.

My thinking in the formula I've used, is to reference the previous month, cell D5, use EOMONTH to return 31-Aug-23, then use MAX to find the last August date that occurs in the ENTRIES table (which won't necessarily be the 31-Aug-23) to then return the corresponding value from the A/c Balance table column in the same row.

Not sure if it is relevant but I insert new lines at the top of the ENTRIES table, so rows are sorted from newest to oldest and also have the times in the same cells, formatted DD-MMM hh:mm.

Thank you for any help.

 

[UpToTheGills]

=MAXIFS(ENTRIES[[A/c Balance],ENTRIES[[Broker Exit Time],">="&D5,ENTRIES[[Broker Exit Time],"<"&EDATE(D5,1))

Feel I'm nearly there with this one but this is returning the largest value in the A/c Balance column in August but I want the formula to return the value of A/c Balance for the last occurrence of an August date.

 

[UpToTheGills]

Need to double-check but I believe I may have nailed it with this formula:

=IF(TODAY()<D6,"",F6/VLOOKUP(MAXIFS(ENTRIES[Broker Exit Time],ENTRIES[Broker Exit Time],">="&D5,ENTRIES[Broker Exit Time],"<"&EDATE(D5,1)),ENTRIES[[Broker Exit Time]:[A/c Balance]],22,FALSE))