Calculating Grades in Excel

What this does

Approximate-match lookup is built for banded conversions. Given a sorted table of lower bounds and their labels, VLOOKUP with TRUE as the last argument finds the largest bound not greater than the score and returns its label. Compared with a chain of nested IFs, the boundaries live on the sheet where anyone can see and change them, and adding a grade is a new row rather than a rewritten formula. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. Treat “calculating grades in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

E2:F6 holds 0/F, 60/D, 70/C, 80/B and 90/A. A score of 87 in B2 with =VLOOKUP(B2,$E$2:$F$6,2,TRUE) returns B. The modern equivalent is =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1), where match mode -1 means exact match or the next smaller item. The cut-offs are the policy, and a lookup table puts them where they can be reviewed instead of burying them inside a formula nobody re-reads. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. Here is the takeaway for “calculating grades in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

Common mistakes

• Leaving the cut-off table unsorted while using TRUE, which returns wrong grades with no error at all.
• Omitting the zero row, so any score below the lowest bound returns #N/A.
• Using FALSE for an exact match, which only ever matches a score that equals a cut-off exactly.
• Nesting IFs in the wrong order — testing >=60 before >=90 gives every high score a D.

Frequently asked questions