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),VLOOKU P(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,Da ta!$J$23:$K$33,2),VLOOKUP(T4,Data!$J$23:$K$33,2),V LOOKUP(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

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 **

"hilarys" wrote:

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),VLOOKU P(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,Da ta!$J$23:$K$33,2),VLOOKUP(T4,Data!$J$23:$K$33,2),V LOOKUP(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

that should have been BA for the 8th grade.
--
** John C **


"hilarys" wrote:

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),VLOOKU P(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,Da ta!$J$23:$K$33,2),VLOOKUP(T4,Data!$J$23:$K$33,2),V LOOKUP(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