INDEX/MATCH combo with using nth result

Previous Test Year:

=INDEX(TestYear,LARGE(IF(OFFSET(TestYear,1,0)="PASS",COLUMN($A$1:$AI$1)),2))

Latest Test Year:

=INDEX(TestYear,MAX(IF(OFFSET(TestYear,1,0)="PASS",COLUMN($A$1:$AI$1))))

Removal Year:

=INDEX(TestYear,MAX(IF(OFFSET(TestYear,1,0)="FAIL",COLUMN($A$1:$AI$1))))

Named Range: TestYear:=Sheet1!$F$1:$AN$1

---

All array formulas, therefore:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Another way.

For Latest Test Year

Formula:

=INDEX($F$1:$AN$1,AGGREGATE(14,6,(COLUMN($F$2:$AN$2)-MIN(COLUMN($F$2:$AN$2))+1)/($F$2:$AN$2="PASS"),COLUMNS(C$1:$C$1)))

Then grab the fill handle and drag to the left to return Previous Test Year.

Years between tests.

Formula:

=C2-B2

Removal year

Formula:

=INDEX($F$1:$AN$1,AGGREGATE(14,6,(COLUMN($F$2:$AN$2)-MIN(COLUMN($F$2:$AN$2))+1)/($F$2:$AN$2="FAIL"),1))

Hi Dave!

Thank you for the response!
I can't seem to fault the formula you have supplied...
I can only fault myself at the moment as I can't wrap my head around 'how' it is working...
Gonna reverse engineer it and see if I can learn something new!

EDIT:
I removed a few $ signs to allow for drag down on asset base.
Is this breakdown of the formula correct?
If so, how would the 2 COLUMNS in the AGGREGATE help with Prev Test Year?
This AGGREGATE function can be used as REFERENCE or ARRAY - this situation array based?
Capture003.PNG

... The formulas above only use COLUMN to AI where as the TestYear data set goes to AN. Adjusting the formula to suit results in #N/A. Is this coincidental as the 5 missing columns = 5 starting columns before the years (A:E vs AJ:AN)?

TextYear is an array spanning columns 6 - 40. However, the elements of that array are accessed by addresses 1 - 35.

The IF condition test returns an array of TRUE or FALSE values depending on the result of the test in each column: {TRUE,FALSE,FALSE,TRUE,FALSE,...,FALSE}.

Note that the elements of the array are counted starting from 1 - 35. In order to get the correct element from the array, I used COLUMNS(A1:AI1) which returns the array {1,2,...,35}. For the example sheet, the array passed to MAX() from the IF() is {1,FALSE,FALSE,4,FALSE,...,FALSE}. MAX() returns 4 to INDEX(), INDEX() returns the forth element of TestYear: 2021.

If you use COLUMNS(F1:AN1), the array would be {6,7,8,...,40}; the IF() would then return {6,FALSE,FALSE,9,...,FALSE} to MAX() which would return 9 to INDEX(), i.e., 2026
COLUMNS(F1:AN1)-5 is another alternative.

($F2:$AN2="FAIL")

Yes. My bad setting that whole range absolute ... force of habit.

(COLUMN($F2:$AN2)-MIN(COLUMN($F2:$AN2))+1)

That's acceptable. I set those absolute as a self-imposed discipline when setting ROW/COLUMN indexes. It's too easy (for me) to overlook when it is an issue.

Hi again.

OR as applied in the latest will return a single TRUE/FALSE. This formula needs an array of TRUE/FALSE ie 1/0. If you are not aware of it "adding" conditions functions as a logical OR and will return that array. To see the effect of that try selecting just (($F2:$AN2="PASS")+($F2:$AN2="FAIL")) in the formula bar and hit the F9 function key. You should see {1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} in the divisor.

With that understanding under 'Latest Test Year:' try this in C2.

Formula:

=INDEX($F$1:$AN$1,AGGREGATE(14,6,(COLUMN($F2:$AN2)-MIN(COLUMN($F2:$AN2))+1)/(($F2:$AN2="PASS")+($F2:$AN2="FAIL")),COLUMNS(C$1:$C$1)))

Under 'Removal year:' (if I understand correctly) simply adding 1 to that formula should do the job.

You are welcome. Thank you for the feedback and marking your thread Solved.