Hello Friends -- first time posting on this forum and thankful for all that you do here. I've been puzzling this out for a while and could use some help.
I have a spreadsheet of employees with benefits change records. My system only tracks start dates for each benefit change, so I need to use the next change record for an employee in my spreadsheet to calculate an end date.
For each record I have the employee number, coverage level and effective date. I'm trying to use INDEX + MATCH + LOOKUP to give me the date of the next record in the range for the employee.
Formula so far is:
=INDEX(D:D,MATCH(D2,A:A)+LOOKUP(D2,A:A)>D2)
Unfortunately, this is returning the same date and not the next largest date in the range. Another version I tried returned the max date. I need the next closest larger date.
Sample Spreadsheet.xlsx
Hope this is clear. Any ideas? This one has me stuck. Thanks so much!
Bookmarks