Custom Formula based on Rank Remarks

[analyst]

Thanks Lalit. I did as you suggested, but in every cell, there is an error - #NAME?.

Revised book is attached. Please guide/do the needful. Thanks a ton for suggestions.

No idea, where i've misdirected myself!

[analyst]

I'm closing this thread, as it is. Thanks for all the support, fellow members. I'm also exiting from this forum, trust no offence.

Once again thanks for all the support that you all extended to me.

Kindly suggest, how do i delete my profile / Deactivate My Account from this Forum

I'll be thankful to the moderators too, if they can directly delete my account/membership, without intimation to me.

[LalitPandey87]

All formulas are array formula so what is missing in your file is:

First select the range A3:A17 Press F2 and then Ctrl + Shift + Enter

Try this and it will solve your problem.

[analyst]

@Lalit,

Thanks Brother. I did Ctrl+Shift+Enter to effect formula as Array Formula, but still error persist.

[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