Calculate Letter Grades (A+ to F) from Numeric Scores (XLOOKUP & VLOOKUP)

Use a complete, explicit grading policy

This example uses thirteen illustrative bands: F below 60; D− at 60, D at 63, D+ at 67; C− at 70, C at 73, C+ at 77; B− at 80, B at 83, B+ at 87; A− at 90, A at 93, and A+ at 97. Replace them with your institution’s approved policy. There is no universal plus/minus grading scale.

D2:E14 holds the minimum score for each band. Each lower limit is inclusive and the next one is exclusive. For example, 87≤score<90 is B+. The highest band includes 100.

Enter scores on one consistent scale

Enter 88 for 88 out of 100, not an Excel percentage literal 88%, whose stored value is 0.88. This worksheet intentionally uses the 0–100 scale. If your source stores fractions, convert them to points by multiplying by 100 before using these formulas.

Blank or nonnumeric scores request a numeric score; negative scores and scores above 100 are rejected. A valid zero earns F. No rounding is applied before grading, so 89.999 remains B+ and 90 is A−.

XLOOKUP: exact cutoff or next smaller cutoff

C2 uses match_mode −1 to find the score’s lower boundary. The default linear search does not require an ascending table, but this shared example deliberately validates ascending order so the same scale also works for VLOOKUP.

The dollar signs lock the scale and validation cell when the formula is copied down. C3 and C4 already demonstrate the same calculation for the other students.

=IF($H$2<>"OK",$H$2,IF(NOT(ISNUMBER(B2)),"Enter numeric score",IF(OR(B2<0,B2>100),"Score must be between 0 and 100",XLOOKUP(B2,$D$2:$D$14,$E$2:$E$14,"Invalid score",-1))))

VLOOKUP: sorted approximate match

F2 uses TRUE for approximate matching and requires ascending minimum scores. H2 checks thirteen numeric cutoffs, a zero first cutoff, strictly increasing order, a highest cutoff no greater than 100, and nonblank labels. Duplicate or reversed cutoffs are rejected rather than producing an apparently valid grade.

H2 checks structure, not whether the labels implement your intended grading policy. Review any policy edits before assigning grades. Keep the cutoff and its label together.

=IF($H$2<>"OK",$H$2,IF(NOT(ISNUMBER(B2)),"Enter numeric score",IF(OR(B2<0,B2>100),"Score must be between 0 and 100",VLOOKUP(B2,$D$2:$E$14,2,TRUE))))

Try the worked examples and a policy edit

Maya’s 88 gives B+, Devon’s 94.5 gives A, and Caleb’s 59.9 gives F in both output columns. Raise D13, the minimum for A, from 93 to 95: Devon becomes A− while A+ still starts at 97.

Try scores exactly at a cutoff and just below it. Clearing a score should display a message, not F. Changing D13 to 97 creates a duplicate cutoff and should block both lookup columns until the scale is corrected.

Further reading

• Microsoft Support — XLOOKUP function