Update of iferror lookup formula by adding max formula

IGNORE
I have not considered the EXM in the header row

off the top of my head
=IFERROR(MAX(LOOKUP(2,1/((ISNUMBER(FIND("EXM",$B$1:$I$1)))*($B2:$I2<>"")*COLUMN($B2:$I2)),$B2:$I2),MAXIFS(B2:I2,B2:I2,">"&60)),"")

BUT i'm sure it can be simplified with just a MAXIFS or ....- i will look into

BUT you are only looking in columns with EXM in the header - which in your example they all have

so it maybe a lot more complicated then i have shown

Try the following formula that is activated by pressing just the Enter key:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

ANS. Post#4

For 365

Cell R2 formula , Drag down

Formula:

Please Login or Register  to view this content.

other version option

Formula:

Please Login or Register  to view this content.

Thank you both for your replies.
I am attaching once again the file with all the possible variables. I made only one change there as I noticed that the formula was not working properly in all cases (in the original file).
I copied one original entry (Rowena) to see if it works in this test file and it works, however it does not in the original one (as in some other cases), so will have to look into it why it gives me random numbers instead of the 67.5.

What I did in this updated file is:
- I added a blank row and a pending row, but no new columns.
- Column R with Final Result has the old formula. This one does not include the max option when the grade is above 60 (see cells in orange).
- Column S has the new formula provided by you. This one seems to work (cells in green are correct vs cells in orange with the old formula) and it now also considers the blanks (meaning it leaves the blank as blank, not 0 - for example case of Ivana), however: when there is written n/a as the last result the formula ignores it instead of considering it as last result. same with 'pending' (cells in red).

So, in other words:
- When the first result is blank leave the Final result blank
- When the result or results are below 60, take the last one (exm 1, 2, 3, 4)
- When the result or results are above 60, take highest one
- if the last result is n/a or pending put this word in the Final result column

I apologise for the confusion and for not placing this file from the beginning. I discovered the other variables when testing the formula.

If there is anything unclear please let me know.

Yes, very confusing!

This, maybe?

=MAX(IF((LEFT($D$1:$Q$1,3)="EXM")*(LEN($D$1:$Q$1)=5),D2:Q2))

Cell T2 formula , Drag down

Formula:

Please Login or Register  to view this content.

OR

Formula:

Please Login or Register  to view this content.

Thank you wk9128. I checked the formula in the test file I provided and it works perfectly!
However when transferring the formula into the original file it gives me very high numbers (like it's adding everything) and the only information that changes (between the test and original file) is this:
LET(a,$D$1:$Q$1,b,$D2:$Q2 - meaning the column number, nothing else (of course I changed them). so for example instead of giving me a result of 62.5 it gives 875 or over 900 or a blank. not sure what could be the reason. Any ideas what can I check?