Search code examples
excelexcel-formulamaxvlookup

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

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

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