Confident Cells

Grade boundaries in Excel: marks to grades

20 November 2026 · 2 min read

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.

ABCDEFG
1PupilMarkGradeMin markGrade
2Maya4760U
3Leah507153
4Omar12U254
5Zara719335
6416
7507
8588
9669
Fictional boundaries for a paper out of 80. Leah's 50 is exactly on the grade 7 boundary, so she gets a 7.

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

Your turn

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.

ABCDEFG
1PupilMarkGradeMin markGrade
2Maya470U
3Leah50153
4Omar12254
5Zara71335
6Ivy33416
7Noah58507
8Ella24588
9669