Custom Formula based on Rank Remarks

[LalitPandey87]

Although the solution which i provided you is perfectly working for me, i think the only issue is you are not familiar with array formula so you can google it.
Here i am posting a new solution based on array formula with some changes

Try this:

For Current Year Table (Change Total Mark Column header to Total Marks)

Step 1:
In Cell A3 put below formula and press Ctrl + Shift + Enter and now drag A3 formula down till you need it

=IFERROR(INDEX($A$20:$F$73,SMALL(IF(($F$20:$F$73="Pass"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROWS(A$3:A3)),MATCH(A$2,$A$19:$F$19,0)),"")

Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank

Step 2:
In Cell D3 put below formula and press Ctrl + Shift + Enter and now drag D3 formula down till you need it

=IFERROR(INDEX($A$20:$F$73,SMALL(IF(($F$20:$F$73="Fail"),(ROW($A$20:$A$73)-ROW($A$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$A$19:$F$19,0)),"")

Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank

For 5 Year Table (Change Total Mark Column header to Total Marks)

Step 1:
In Cell H3 put below formula and press Ctrl + Shift + Enter and now drag H3 formula down till you need it

=IFERROR(INDEX($H$20:$M$73,SMALL(IF(($M$20:$M$73="Pass"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$H$19:$M$19,0)),"")

Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank

Step 2:
In Cell K3 put below formula and press Ctrl + Shift + Enter and now drag K3 formula down till you need it

=IFERROR(INDEX($H$20:$M$73,SMALL(IF(($M$20:$M$73="Fail"),(ROW($H$20:$H$73)-ROW($H$20))+1,""),ROWS(D$3:D3)),MATCH(D$2,$H$19:$M$19,0)),"")

Now drag the selected cells(where you draged the formula) for next two colums which are Total Marks and Score Rank


As all these are array formula so make sure you are applying the formula with Ctrl + Shift + Enter