Custom Formula based on Rank Remarks

[analyst]

I've sheet1 where Students Name, Marks, Passing Status etc (Pass, Fail, ATKT) are given based on some Ranking system, for current Year (Range A19:F73), and for last 5 years (Range H19:M73).

Row data which would always starts from ROW18, could increase/decrease in future based on number of students.

Row 1 to 17 is like DASHBOARD, where I want summary of students who have passed, or failed based on Score Rank (this is in Column E & L respectively),

So, those who have 'Pass' with high 'Score Rank' his name, total mark, and SCORE RANK should be part of DASHBOARD
So is the case with those who have 'Fail' with High 'Score Rank'

Currently, I've done it manually in DASHBOARD to show how the final output should look like.

What formula should I have in place in each Matrix (A3:C17), (D3:F17), (H3:J17), and (K3:M17) to get above

Sample file is attached.

I dont know which formula should i use like, LARGE, OFFSET, IF(AND) or combination to get the desired result.

P.S.: If possible, kindly make formula flexible, so that, in case i need similar table for those who have remarks 'ATKT' can also be made similarly.