More data identification formula questions - Compare, Validate and Display

[Doofus1]

Folks, I am going to need a lot of help before I can ever become an useful contributor to this forum.
But I am trying to learn.
So, I have a file that I am working with/on.
With this file I think of what I want to do and I then try to see if the formulas are possible. And that's why I keep pestering all of you.

So here are my two new questions. ( I have uploaded an excel file to explain and give you a visual)

In column M
1. Identify the student's highest score [column H] (in case of tie, select most recent highest score).
2. If that score was earned within the last 50 day's (column J), display "OK" else "NO".
3. The "OK" or "NO" must be displayed in the top most cell in column M - for each student.

In column N
1. Identify the student's highest score [column H] (in case of tie, select most recent highest score).
2. If that score was earned within the last 50 day's (column J), display the name of the subject (column F) "Biology, Physics etc" else " blank".
3. The "SUBJECT NAME" or " blank" must be displayed in the top most cell in column N - for each student.

Thank you

[PaulM100]

Maybe something like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Both are array formulas entered with Ctrl+Shift+Enter instead of regular Enter

[JohnTopley]

Try (shorter versions of Paul's formulae)

in M4

=IFERROR(IF(D3<>D4,IF(INDEX($J$4:$J$95,MATCH($D4&MAX(IF($D$4:$D$95=D4,$H$4:$H$95)),$D$4:$D$95&$H$4:$H$95,0))<=50,"OK","NO"),""),"")

in N4

=IF(M4="OK",INDEX($F$4:$F$95,MATCH($D4&MAX(IF($D$4:$D$95=D4,$H$4:$H$95)),$D$4:$D$95&$H$4:$H$95,0)),"")

BOTH are array formula

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Results in columns O & P of attached

[Doofus1]

Thank you. I appreciate the formula structure.

[Doofus1]

Once again Mr Topley, Thank you for an elegant formula.!!!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks