INDEX/MATCH combo with using nth result

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.