# 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