Bottom up search/ with multiple criteria

[M1225]

Hello!

Here attached is my table.

This is a table that get updated every day. Every time the pilot or copilot make a landing, it is recorded. For accuracy, pilots need to complete 3 landings every 90 days. I am trying to find a formula that will give me the date of the last 3rd landing, then add +90 days to keep track of the expiration of this accuracy.

The formula has to start from bottom.
It does not matter if the landing is completed as a pilot or copilot. Sometimes the pilot(or copilot) fly but doesn't complete the landing.
Sometime the pilot complete more than 1 landing by flight date.

As an example, I have painted "Alex" in blue (and Jordan in yellow) of what should correspond of his last 3rd landing.

I have tried LARGE/SMALL, combination of index/match formulas, but didn't quite make exactly what I wanted. I had something like this:
=LARGE(IF("Pilot"=Alex,ROW("Pilot")-MIN(ROW("Pilot"))+1),3)

But I it is giving me the row number (and I want the data from "Dates" column) and It is accounting for the last 3rd in flying, not the last 3rd landing.

Thank you for your help


https://paste.pics/d5f21e4b51c92f702cb7b7b813ba2450

 

[dUBBINS]

I don't want to click on your link. But if you are getting back the row number, you can imbed what you have inside of =INDEX(). Just put your entire formula where the syntax tells you to put the ROW_NUM. I hope that gets you on the right track.

 

[Aladin Akyurek]

Try to post your data in an Excel readable form.