Forum Discussion
Search 3rd from bottom, then revert a date depending on a quantity
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 :)
Hello,
this would be easier if you attached the workbook instead of a screenshot of the workbook.
Solving this with a formula is extremely difficult because of the arrangement of the data in the different columns. This data table is more like a report than a data input for evaluation.
You need to transform your data into a flat table with columns for date, Name and landings. This can be done with Power Query. Now you can use a formula to return the third last date like this:
=INDEX(landings[date],LARGE(IF(landings[Name]=F4,ROW(landings[Name])-MIN(ROW(landings[Name]))+1),3))
Just add a +90 at the end of this formula to add 90 days.
Here is a screenshot, and I also attach the file with the Power Query transformation.
