I have a table with landowners in column A. In column E it shows a start date of when landowner entered a status (outreach, negotiating, signed). I want to dynamically populate and end date based on the next instance of the landowner in a list, subtract the start date by 1 day for that next instance and place that date into the previous landowner row.

blue table is what I expect to see after using this formula, but you can see that it isn’t working properly, it’s not populating date -1 in row 2, it’s doing it when it finds the next landowner (row 5).

=IF(COUNTIF(A2,A2)>1,INDEX(D2:D,MATCH(A2,A2:A,0))-1,"")

Here is the link, it’s sheet6 that has the tables and calc