I recently asked a similar question, and found that the answer was not intuitive to me--I'm still trying to understand it. That said, paste the following code into B18.
=IFERROR(INDEX($B$2:$B$13,SMALL(IF($C$2:$C$13="Fail",IF(COUNTIF($B$17:B17,$B$2:$B$13)=0,ROW($A$2:$A$13)-ROW($A$2)+1)),1)),"")
While editing the cell, confirm it as an array formula by pressing Ctrl+Shift+Enter. Now drag the contents from B18 through B28. Columns A, C, and D can be paired using an Index and Match combination, as shown in the attached file.
Bookmarks