Letter Grade Formula in Excel: IFS or a Lookup Table

A grade formula in Excel turns a number score into a letter such as A, B or C. There are two good ways to write one: an IFS formula that tests the grades from the top down, or a small grade table with VLOOKUP. This guide shows both on the same scores, explains which to pick, and covers the mistakes that give students the wrong letter.

The grading scale and sample data#

Every example uses this common scale: 90 or more is an A, 80 to 89 is a B, 70 to 79 is a C, 60 to 69 is a D, and anything below 60 is an F. The scores sit in A1:B7:

The correct grades are: Maya A, Tom C, Lina B, Omar F, Grace C and Ravi D. Grace's 70 is a useful check, because it sits exactly on the line between D and C.

How to write a grade formula with IFS#

IFS takes pairs of a test and a result, and returns the result for the first test that is TRUE. In C2, type:

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")

Press Enter, then fill down to C7 (Windows: Ctrl+D; Mac: ⌘D). Here is how Excel reads Tom's 78:

1. Is 78 >= 90? No, move on.
2. Is 78 >= 80? No, move on.
3. Is 78 >= 70? Yes, so return C and stop.

The last pair, TRUE,"F", is the catch-all. TRUE is always true, so any score that failed every test gets an F. Without it, Omar's 59 would return #N/A, because IFS has no built-in "otherwise" result. The full guide to the IFS function explains this in more detail, and the IFS function reference lists the syntax.

IFS needs Excel 2019, Excel 2021 or Microsoft 365. In Excel 2016 or older, it shows #NAME?.

Why the order of the tests matters#

IFS and nested IF stop at the first TRUE test, so the highest grade must come first. Look at what happens if you write the tests from the bottom up:

=IFS(B2>=60,"D",B2>=70,"C",B2>=80,"B",B2>=90,"A",TRUE,"F")

Maya's 93 is greater than 60, so the first test is TRUE and she gets a D. Every score of 60 or more gets a D. The formula has no error message, which makes this mistake easy to miss. Always test the biggest limit first and work down, and use >= so a score exactly on the limit gets the higher grade.

The nested IF version for older Excel#

A nested IF gives the same results and works in every version of Excel. Each IF sits in the "otherwise" slot of the one before:

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

Count the brackets: four IFs need four closing brackets at the end. The final "F" plays the same role as TRUE,"F" in IFS. Use this version if the file will be opened in Excel 2016 or earlier. The guide to nested IF for grades and bands walks through it step by step.

How to use a lookup table for grades#

A grade table puts the scale in cells instead of inside the formula, so you can change a limit without editing anything else. Type the minimum score for each grade in E2:E6 and the letter next to it in F2:F6, sorted from the smallest minimum to the largest:

Then in C2, type this and fill down:

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

With TRUE as the fourth argument, VLOOKUP does an approximate match: it finds the largest minimum that is less than or equal to the score. For Tom's 78, the largest minimum not above 78 is 70, so VLOOKUP returns C. The $ signs lock the table so it doesn't slide down as you fill.

Two rules make this work:

• The first column must be sorted from smallest to largest. If it isn't, VLOOKUP can return the wrong letter with no warning.
• The first row must start at 0 (or the lowest possible score). A score below the first minimum returns #N/A.

The article on VLOOKUP approximate match for tiers explains how the match works, and the VLOOKUP function guide covers every argument. The same table method also works for sales bands in a commission calculator.

In Excel 2021 or Microsoft 365, XLOOKUP can read the same table with match mode -1 (exact match or the next smaller item): =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1).

IFS or a lookup table: which should you use?#

Use IFS for a short, fixed scale that nobody will change. Use a lookup table when the scale might change, has many grades, or is shared by several sheets.

With plus and minus grades, a scale can grow to a dozen or more bands. A formula that long is hard to read and easy to break, while a table just gets more rows.

Common grade formula problems#

Most wrong grades come from a few small issues:

1. Decimal scores. A score of 89.6 is not >= 90, so it gets a B. If your school rounds first, use ROUND(B2,0) inside the formula: =IFS(ROUND(B2,0)>=90,"A",...).
2. Blank scores. An empty cell counts as 0 and gets an F. Add a check first: =IF(B2="","",IFS(...)).
3. Scores stored as text. A score typed with a space or imported as text may not compare as a number. Convert it with VALUE.
4. An unsorted grade table. Sort the minimum scores from smallest to largest before using VLOOKUP with TRUE.

Once the letters are in place, =COUNTIF(C2:C7,"C") counts the C grades (2 here: Tom and Grace). To put the grade formula into a complete sheet with weighted marks and class averages, follow the guide to building a gradebook in Excel. If your grades are numbered levels, the article on the CHOOSE function shows another way to map a number to a label.

Practise it#

Grade formulas are a favourite in job tests because they check logic and lookups at once. Practise nested IF and IFS in a live spreadsheet: you type the formula and it checks it instantly. Then build the table version in the VLOOKUP approximate match lesson, or start with the exercise on this page.