Finding the closest date, that is most recent date

I have the following table, table1:

date	value
05/04/2022	400
22/04/2022	312
04/05/2022	942
06/08/2022	231
12/08/2022	243

I want to get the most recent date in the table (in this case 12/08/2022) and minus 1 year from it to get 12/08/2021. Then look at the table to find the value that is most closest to 12/08/2021. In this case, the closest value is from the table is 05/04/2022, so the formula will return 400.

I tried with this formula, but really unclear how to do it.

=VLOOKUP(IFERROR(MAX(Table1[date])-365,MAX(Table1[date])), Table1, 2,TRUE)

I have Microsoft Excel 2020

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(mostRecentDate,MAX(table1[date]),
     previousYearDate,mostRecentDate-365,
     MIN(FILTER(table1[date],table1[date]>previousYearDate))
     )

If you don't have the FILTER-function try this:

=INDEX(table1[date],MATCH(MIN(ABS(table1[date]-(MAX(table1[date])-365))),ABS(table1[date]-(MAX(table1[date])-365)),0))

It checks for the differences to the max-date - 365