Assistance with returning cell data base on most recent day in specified year.

J_S1

I am trying to create a formula on a sheet summary that returns the information in my row titled Total that corresponds with the most recent date within a specified year. I came up with the following formula but it is returning #No Match

=INDEX([Total]:[Total], MATCH(MAX(COLLECT([Report Date]:[Report Date], [Report Date]:[Report Date], CONTAINS("24", @cell))), [Total]:[Total], 0))

If anyone has any insight I would greatly appreciate the help :)

Nick Korna

Hi @J_S1,

Use a helper column to get the year from your report date:

=YEAR([Report Date]@row

You can then get the value of the latest date in a given year with a formula like this:

= INDEX(COLLECT(Total:Total, [Report Date]:[Report Date], MAX(COLLECT([Report Date]:[Report Date], Year:Year, [Year to check]@row))), 1)

Sample:

If you want to look at only total, then you could change the [Year to check]@row to a fixed cell instead.

Hope this helps, but if you've any problems/questions then just let us know!