To assign a letter grade from a numeric score in Excel, put the grade cutoffs in ascending order and use an approximate-match VLOOKUP. If the score is in B2 and the cutoff-and-grade table is in E2:F6, enter =VLOOKUP(B2,$E$2:$F$6,2,TRUE); a score of 87 returns B when the table lists 80 as the B cutoff.
What VLOOKUP does in a grading sheet
VLOOKUP searches down the first column of a table for a value, then returns a value from another column in the same row. In a grading sheet, the searched value is usually a student’s score, while the returned value is the corresponding letter grade. This is a lookup, not a grading policy: the formula applies the cutoffs you provide and does not decide what those cutoffs should be.
A threshold table might look like this:
- 0 — F
- 60 — D
- 70 — C
- 80 — B
- 90 — A
Each number is the minimum score for its grade. With this scale, 60 through 69.99 earn D, 70 through 79.99 earn C, and so on. The grade boundaries are an example only; use the rubric or course policy that actually applies. A grading formula can be mathematically correct and still produce the wrong result if the table does not reflect that policy.
Set up the score and cutoff table
For the example formula, put students’ numeric scores in B2:B20. Put the minimum score for each grade in E2:E6 and its letter in F2:F6. The first row of the lookup range should contain the first cutoff, not a heading: use the two-column range E2:F6 for the formula. If you want headings such as Minimum and Grade, place them in E1:F1, outside the lookup range.
Sort the cutoff values from smallest to largest: 0, 60, 70, 80, 90. That order is essential for approximate-match VLOOKUP to return the correct grade. Keep each cutoff paired with its grade when sorting; otherwise, Excel could return a letter attached to the wrong threshold. You can use decimals if the rubric has decimal boundaries, such as 89.5, but the values must still be in ascending order.
Make sure the score and cutoff cells contain numbers, rather than text that merely looks like numbers. One way to check is to select a cell and look at its value in the formula bar; imported data can sometimes have inconsistent types. Also agree on how to handle missing scores before filling the formula down. A blank score should not silently be treated as a real zero if the student has not yet been graded.
Enter and copy the grading formula
With scores in B2:B20 and the sorted minimum-score/grade table in E2:F6, enter =VLOOKUP(B2,$E$2:$F$6,2,TRUE) in C2. For a score of 87 in B2, Excel returns B. The argument 2 tells Excel to return a value from the second column of the lookup range, which is column F; TRUE requests approximate matching.
The dollar signs make the lookup range absolute. When you copy the formula down from C2, the score reference B2 changes to B3, B4, and so on, while the cutoff table remains E2:F6. After entering the formula, press Enter, select C2, and drag the fill handle at the lower-right corner down the grade column. You can also copy C2 and paste it into the cells below.
The arguments follow this pattern: the score to look up, the table range, the number of the return column counted from the left edge of that range, and the match setting. In this example the table starts in E, so F is column 2 of the selected range. It is not column 6 merely because F is the sixth worksheet column. Changing the selected range or column order may require changing the column number.
Why approximate match is right for grade cutoffs
For a threshold scale, VLOOKUP with TRUE finds an exact cutoff when one exists; otherwise, it uses the largest cutoff that is less than the score. Thus, a score of 87 matches the 80 row and returns B. A score of exactly 90 matches the 90 row and returns A. The default match setting is approximate, so writing TRUE explicitly makes the intended behavior easier to see and reduces the risk of someone mistaking the formula for an exact lookup.
FALSE means exact match: Excel returns a result only when the score equals a value in the first column of the table. That is useful when searching for an exact student ID, course code, or other distinct value, but usually wrong for grade bands. A score of 87 is not equal to any of the example cutoffs, so an exact-match grade formula would return #N/A instead of B.
Approximate matching has important edge behavior. A score below the lowest threshold, such as -1 when the first cutoff is 0, returns #N/A because there is no lower cutoff to use. A score above the highest threshold, such as 105 when 90 is the final cutoff, returns the grade associated with 90. Decide whether those outcomes fit the rules for your sheet; if scores must be from 0 to 100, you may want to validate or review out-of-range entries rather than allowing them to pass unnoticed.
Common grading formula problems
- Cutoffs are not sorted. Approximate match relies on ascending thresholds. Reorder the table and keep each grade beside its correct cutoff.
- The range moves when copied. Use dollar signs around the lookup range, as in $E$2:$F$6, so it does not shift down with each student row.
- The wrong column number is used. Count from the left edge of the selected range. In E2:F6, the grade column is 2.
- There is an unexpected #N/A. Check for a score below the lowest cutoff, text-formatted numbers, or an incorrect range that omits a threshold.
- A score is treated as a different value than expected. Check whether the rubric uses whole numbers, decimals, or rounded scores. For example, 89.9 is below a 90 cutoff unless the score is rounded before lookup.
Do not assume that rounding is part of VLOOKUP. It compares the value in the score cell as stored. If the grading policy says to round first, make that step explicit and consistent with the policy. Likewise, if the policy uses plus and minus grades, list every applicable lower boundary in the table—for example, separate thresholds for B-, B, and B+—rather than expecting VLOOKUP to infer them.
Separate grade lookup from weighted-score calculation
A common source of confusion is asking VLOOKUP to calculate a course score. VLOOKUP only maps an already calculated value to a category. If assignments have different weights, first calculate the final numeric score according to the course rules, then look that score up in the grade table. For example, a student could have an 80 on a test and a 95 on a project; the final score depends on the assigned weights, not on VLOOKUP.
For a simple two-part illustration, suppose a test score is in B2, a project score is in C2, and the two scores each count for half. With those values in B2 and C2, the formula =B2*50%+C2*50% returns 87.5 when the test is 80 and the project is 95. That result can then be placed in a score cell and looked up using the cutoff table. This is only an example of equal weighting, not a recommendation: use the actual weights and calculation rules for the class, including any rules for missing work or extra credit.
VLOOKUP limits and alternatives
VLOOKUP works well when the lookup value is in the leftmost column of the selected range and the value to return is to its right. It cannot look left within that range. Its numbered return-column argument can also become fragile if columns are inserted or the table is reorganized. For a small, stable grade table, those limits may be perfectly manageable; clear labels and a carefully selected range matter more than using a newer function for its own sake.
In Microsoft 365 for Windows, XLOOKUP is another option and can return values from a separate column without a numeric column index. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019. Excel for the web and current Microsoft 365 for Mac also offer XLOOKUP. If a workbook must be opened in older Excel versions, VLOOKUP is often a more compatible choice. Whatever function you choose, keep the grading thresholds visible and verify the policy boundaries.
For people learning spreadsheet skills, VLOOKUP is also useful beyond grading: the same pattern can map a product code to a price or a department code to a name. The lesson is to identify what is being searched, ensure it is in the first column of the chosen range, and decide whether the match must be exact or based on a threshold.
Frequently Asked Questions
Should I use TRUE or FALSE for letter grades?
Use TRUE for a conventional cutoff table where each row is a minimum score for a grade, and sort the cutoffs ascending. Use FALSE when the score must exactly equal a table entry, which is generally not how grade bands work.
Why does VLOOKUP return the wrong grade?
Check that the first column of the lookup table contains ascending numeric cutoffs, that each cutoff is paired with the intended letter, and that the formula uses the correct return-column number. Also check whether the score is rounded or stored as text contrary to the intended grading rules.
Can VLOOKUP assign plus and minus grades?
Yes. Add a row for every lower boundary in the scale, in ascending order, with the appropriate letter grade beside it. Approximate matching then returns the grade attached to the highest boundary that does not exceed the score.
What does #N/A mean in a grading lookup?
It means Excel did not find a usable match. With approximate matching, first check whether the score is below the smallest cutoff. Also verify that the lookup range includes the score table and that the values are numeric and correctly arranged.
Does VLOOKUP calculate a weighted course grade?
No. It looks up a value and returns a corresponding table entry. Calculate the weighted numeric score separately using the course’s actual weighting rules, then use that result as the lookup value.
Related
- Excel Budget Spreadsheet Free? Find Templates and Tips
- Excel Accounting Format: How It Works and When to Use It Properly
- Excel Accounting Format: Show Negative Numbers in Brackets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Not Calculating Until You Press Enter?If an Excel formula updates only after you press Enter, first check whether calculation is set to Automatic:…
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- Excel COUNTIFS Not Equal to Text: Formula ExamplesTo count cells that are not equal to specific text, use COUNTIFS with the not-equal operator inside quotation…
- XLOOKUP Examples for Beginners: Formulas, Tips, and FixesXLOOKUP finds a value in one range and returns the corresponding value from another; for example, with product…
- Excel Time Formulas: Calculate Overnight PM-to-AM HoursIf A2 contains a PM start time and B2 contains the following AM finish time, enter =MOD(B2-A2,1) to…
- Excel SUMIF Not Blank: Sum Values When Cells Have DataWith the cells to check in A2:A10 and the numbers to add in B2:B10, use =SUMIF(A2:A10,"<>",B2:B10) to sum…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.