Obtain Most Recent Value based on date of within Month

Preston B

Hello,

I am trying to obtain the most recent membership entered for the month, where the value doesn't matter if largest. The formula will be used to calculate the most recent membership of the month, for the calendar year from the Report Date.

I was able to use =MAX(COLLECT({Membership}, {Month}, "January")) but comes out to 300 and not 275. I can't seem to figure out the formula needed to modify.

{Membership}= Membership Value

{Month}=Month Helper Column

Paul Newcome

Try somethign like this instead:

=INDEX({Membership}, MATCH(MAX(COLLECT({Report Date}, {Month}, @cell = "January")), {Report Date}, 0))

Preston B

Thank you, for some reason it's coming back blank. I have another sheet where I am posting the formula.

Any Clue? and Appreciate the assistance.

Paul Newcome

Are you able to provide a screenshot of the source sheet filtered to only show rows where the Month column is "January"?

Preston B

Ok i see what happened, i had test entry for 2024 that had blank membership data, which when i removed it got the right information!

I tried to add a secondary criterion to consider the year as below but got an invalid operation.

=INDEX({Membership}, MATCH(MAX(COLLECT({Report Date}, {Month}, @cell = "January", {Report Date}, {Helper Year}, @cell = "2023")), {Report Date}, 0))

Any advice and appreciate the assistance.

Paul Newcome

Your syntax is off. Try this instead:

=INDEX({Membership}, MATCH(MAX(COLLECT({Report Date}, {Month}, @cell = "January", {Helper Year}, @cell = "2023")), {Report Date}, 0))