Grade boundaries in Excel: marks to grades
Put the grade boundaries in a small table, lowest mark first, then look each mark up with an approximate match: =VLOOKUP(B2,$F$2:$G$9,2,TRUE). Excel finds the highest boundary that the mark reaches, so a mark of 47 with a grade 6 boundary of 41 and a grade 7 of 50 gets a 6.
The boundary table
One row per grade, with the lowest mark that gets it, sorted from smallest to largest. Start with 0 so every mark gets a grade.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Pupil | Mark | Grade | Min mark | Grade | ||
| 2 | Maya | 47 | 6 | 0 | U | ||
| 3 | Leah | 50 | 7 | 15 | 3 | ||
| 4 | Omar | 12 | U | 25 | 4 | ||
| 5 | Zara | 71 | 9 | 33 | 5 | ||
| 6 | 41 | 6 | |||||
| 7 | 50 | 7 | |||||
| 8 | 58 | 8 | |||||
| 9 | 66 | 9 |
How the approximate match works
With TRUE at the end, VLOOKUP goes down the first column until the next boundary is bigger than the mark, then stops. For Maya's 47: past 41 (grade 6), but 50 is too big, so 6.
XLOOKUP can do the same with match mode −1, "exact or next smaller": =XLOOKUP(B2,$F$2:$F$9,$G$2:$G$9,,-1). That version doesn't need the table sorted.
Why not nested IFs?
=IF(B2>=66,9,IF(B2>=58,8,…)) works, but needs eight IFs, and every boundary change means editing every formula. With a table, you change one cell when boundaries come out, and every grade updates.
Marks to the next grade
Once you have the grade, the next boundary up is the smallest one bigger than the mark: =MINIFS($F$2:$F$9,$F$2:$F$9,">"&B2)-B2. Maya needs 50 − 47 = 3 more marks for a 7. The mark sheets lesson builds the whole sheet.
Common mistakes
FALSE at the end
FALSE looks for an exact match, so only marks that sit exactly on a boundary find a grade. The rest show #N/A.
Boundaries not sorted
With TRUE, the first column must go from smallest to largest, or VLOOKUP gives wrong grades with no warning.
The table moves when copied
Lock it with $ signs (see $ and F4).
Try it now
The grade boundaries are in F2:G9, with the lowest mark for each grade. In C2, look up each pupil's grade from their mark. Your formula will be copied down to C8.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Pupil | Mark | Grade | Min mark | Grade | ||
| 2 | Maya | 47 | 0 | U | |||
| 3 | Leah | 50 | 15 | 3 | |||
| 4 | Omar | 12 | 25 | 4 | |||
| 5 | Zara | 71 | 33 | 5 | |||
| 6 | Ivy | 33 | 41 | 6 | |||
| 7 | Noah | 58 | 50 | 7 | |||
| 8 | Ella | 24 | 58 | 8 | |||
| 9 | 66 | 9 |