Look up a grade letter from a score in Excel with VLOOKUP
A score becomes a letter by falling into a band: 80 and above is an A, 70 and above a B. That is an approximate-match lookup — the same shape as a price band table, with the band starts as minimum scores.
Written 12 November 2026
Result
| Farah Iqbal, score 85 | A |
| Amara Osei, score 47 | F |
| A grades in the class | 2 |
| Class average score | 68.63 |
Download the spreadsheet: grade-letter-from-score.xlsx (5,464 bytes)
It contains the eight students, the grade band table, the grade counts, and the class average.
1What you need
- Excel, Google Sheets, or LibreOffice. VLOOKUP, COUNTIF, and AVERAGE work the same in all three for this tutorial.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2The band table
A grade band is a minimum: 80 and above is an A, 70 and above is a B, and so on down to F, which starts at 0 so that every possible score has a grade. The table sits in E4:F8:
| Min score | Grade |
|---|---|
| 0 | F |
| 50 | D |
| 60 | C |
| 70 | B |
| 80 | A |
The minimum scores run in ascending order, smallest first. An approximate-match lookup assumes the first column is sorted that way (VLOOKUP function — Microsoft Support) — type the bands top-grade first and the lookup hands out wrong letters.
3Grade one student
C4 =VLOOKUP(B4,$E$4:$F$8,2,TRUE)The formula looks up the student's score in the band table and returns the grade from column 2. TRUE asks for an approximate match, which returns the grade for the largest band start that is not above the score. Filled down over the eight students, the grades come out:
| Student | Score | Grade |
|---|---|---|
| Amara Osei | 47 | F |
| Ben Kowalski | 52 | D |
| Chloe Martin | 64 | C |
| Dev Patel | 71 | B |
| Eli Thompson | 79 | B |
| Farah Iqbal | 85 | A |
| Gus Novak | 93 | A |
| Hana Kim | 58 | D |
Eli Thompson's 79 is the row to learn from: a B, not an A, because the A band starts at 80 and 79 has not reached it. A score that lands exactly on a band start takes that band — 80 itself would be an A.
4Count the grades
COUNTIF counts the cells in a range that meet a criterion (Statistical functions (reference) — Microsoft Support). One formula per grade counts the class:
B14 =COUNTIF($C$4:$C$11,A14)The counts are A 2, B 2, C 1, D 2, and F 1 — eight students, which is the check that every score received exactly one grade.
5The class average
B19 =AVERAGE(B4:B11)The eight scores average 68.63 — a C-class average, sitting just under the B band the way the individual grades suggest.
Traps
Reading the bands as “up to”. In this table 50 means “50 and above earns a D”. Build the table as maximums instead — F up to 49, D up to 59 — and an approximate match hands a score of 52 the F band's letter, one row too low.
Using FALSE. Exact match finds a grade only for a score that is itself a band start: 47, 52, 64, and most other real scores return #N/A.
Leaving out the F band. A table that starts at 50 gives the lowest scores nothing to match: with TRUE, a lookup value smaller than the smallest first-column value returns #N/A (VLOOKUP function — Microsoft Support). The 0 row is what makes the table cover everyone.
Sources
- VLOOKUP function — Microsoft Support — syntax, approximate match and the sorted first column, and the #N/A result below the smallest band start.
- Statistical functions (reference) — Microsoft Support — COUNTIF counts the cells in a range that meet a criterion; AVERAGE returns the average of its arguments.