Well done Kamaruddin
.
I will try to explain.
First Part:
We created a simple lookup table for each grade (except for the lowermost and topmost grade) so that when it is applicable to for an upgrade we simply search the higher grade written against it. This portion is handled by the simple VLOOKUP formula.
VLOOKUP(C6,$O$3:$P$10,2,0),C6)
Second Part:
We needed to figure out in which cases we need to apply formula written in the first part. This is where the combonumber (it is just a phrase I assigned. There is no such theory) came into the picture. In your case the constraint was 0 to 2 number of subjects to be upgraded. However, the formula can work for any number of upgrades up to 5 since we have 5 subjects.
The combonumber basically builds up the ranking out of the criteria in their specified order i.e. the topmost criterion has the highest value. Or we can call them priority.
Here the first criterion was grade (which was non numeric) so it needed to be converted to numeric result which we could use in the combonumber. So again we used a function MATCH to create numeric grade.
=IFERROR(MATCH(C6,$O$4:$O$10,0),0)
Based on position it assigns higher numeric index for lower grade. So the lowest grade subject gets highest priority (1 for A2 to 7 for E1) while upgrading.
=Numeric grade * 100000000
Note: It also meant that grade A1 and E2 would get 0 here and would never be in possibility for upgrade which was another requirement.
If there were more than specified subjects which were eligible for upgrade then we needed to look at 2nd criterion of overall marks.
= Overall Marks * 1000000
If these both matched then we went to third condition.
= SA Marks * 10000
If there was a tie then we'd explore 4th one.
= FA Marks * 100
And then here we checked 5th one.
= XYZ
And if there still was a tie then we then broke it by their order in the spreadsheet and for this distinguishing we create a sliding COUNTIF which subtracted the COUNT from above product sum.
COUNTIFS($F$6:F6,F6,$G$6:G6,G6,$H$6:H6,H6,$J$6:J6,J6)
This gave us a unique priority number for each subject its eligibility order like below:
272403267
Now you can identify each colored group it relates in sequence. It is as if each number comes into picture only if needed until then we do not need to look at it.
And then we simply used LARGE function's built in capacity to see if the combonumber created for subjected was greater than the number returned by LARGE function. e.g.
=LARGE(num_array,2)
returns 2nd largest value from num_array.
Hope this helps you. If something still remains unclear then please ask.