Index and matching column A values to populate date -1 from next found date?

Question

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

Answer 1

You can try this formula:

=ArrayFormula(LET(landOwner,A2:A8,
                  startDate,D2:D8,
                  tableId,landOwner&"-"&TEXT(startDate,"MM-DD-YY"),
                  lookupId,landOwner&"-"&TEXT(startDate+1,"MM-DD-YY"),
                  endDate,XLOOKUP(lookupId,tableId,startDate-1,,1),
                  getLandOwner,XLOOKUP(lookupId,tableId,landOwner,,1),
                  finEndDate,IF(getLandOwner=LandOwner,endDate,),
                  finEndDate))

This solution consists of 5 parts. Let's break this down:

First, we create a new id for the table using the Landowner and Start Date. We'll also format the date using the TEXT Function so that there will be no problem in terms of order:

2nd, we also need to create an Id that we can use to look up the end date. We'll use the same method above, but we'll add a day to the start date so that we can utilize a certain method in the XLOOKUP later:

3rd, we perform the XLOOKUP where we use the match_mode = 1, which means an exact match or the next value that is bigger than the search_key. This is the reason why we added a day to the start dates so that we won't find an exact match but instead the next value that is larger.

4th, the XLOOKUP above has a caveat. The next value that is larger than the lookup id can be a different landowner (ex: the value that is larger than A-01-31-24 is B-01-05-24). Therefore, we also need to extract the Landowner using the same XLOOKUP above so that we can check it:

5th, now, we finalize the end date by checking if the Landowner is the same as the Landowner from the XLOOKUP. If true, we return the initial end date. Otherwise, we return blank.

That's all. Note that the formula is an array formula that generates the list of end dates.