Best grade using MAX ...BETTER WAY?

[hilarys]

I have a spreadsheet with pupils marks and grades for many exams. I need to
find their best grade A*- U over 8 exams. The grade records are in in every
4th column going across the spreadsheet.
At present I am using VLOOKUP to assign each grade a number and then using
the MAX function to find the highest grade 'number' , which is then converted
back to a grade'letter' using VLOOKUP.

=VLOOKUP(MAX(VLOOKUP(A4,Data!$J$23:$K$33,2),VLOOKUP(D4,Data!$J$23:$K$33,2),VLOOKUP(H4,Data!$J$23:$K$33,2),VLOOKUP(L4,Data!$J$23:$K$33,2),VLOOKUP(P4,Data!$J$23:$K$33,2),VLOOKUP(T4,Data!$J$23:$K$33,2),VLOOKUP(X4,Data!$J$23:$K$33,2),VLOOKUP(BA4,Data!$J$23:$K$33,2)),Data!$M$23:$N$33,2)

Can anyone suggest a better way of doing this seems very long winded!

Many thanks

 

[John C]

2 questions:
1> What are the actual grades? I noticed you have your vlookup table as 11
rows, but if grades are standard, A+, A, A-, etc.... then should have more.
2> You say every four column is a grade, but you start with column A, then
to D, which is column 1, then 4, then you go every 4 columns, is this
accurate? Grades are in columns: A, D, H, L, P, T, X, PA

 

[John C]

that should have been BA for the 8th grade.