Finding the closest date, that is most recent date – 1 year's value

I have the following table, table1:

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

>Solution :

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