Function Problems

How to Convert Percentages to Letter Grades in Excel with XLOOKUP or VLOOKUP

Jason Miller Oct 14, 2026 870 views

Question details

The user needs to convert student percentage scores into corresponding letter grades using lookup functions without triggering #N/A or #VALUE! errors.

How to Convert Percentages to Letter Grades in Excel Using XLOOKUP or VLOOKUP

Product: Excel
Device & OS: not provided
Scenario: Creating an automated Excel gradebook to assign letter grades based on changing percentage thresholds.
Observed behavior: Lookup formulas sometimes return #N/A or #VALUE! errors when the percentage thresholds are arranged incorrectly or when approximate match settings are improperly configured.

Before you start

Ensure your grading threshold table is set up with the minimum percentage required for each letter grade, and critically, verify that the percentage column is sorted in ascending order (from lowest to highest).

Solution 1Recommended

Use XLOOKUP to Find Exact or Next Smaller Values

XLOOKUP is the most robust and flexible function for finding exact matches or the next smaller item in modern spreadsheet software like Microsoft 365 and WPS Office.

XLOOKUP replaces traditional lookup functions by offering advanced match modes. By using the -1 match mode, you instruct the formula to drop down to the next smaller percentage threshold if the student's exact score isn't listed in your reference table.

Create a Reference Table

Create a table containing the lower percentage boundary for each grade, sorted in ascending order. For example, name the table 'tblGrades' with a 'Percent' column and a 'Grade' column.

Enter the XLOOKUP Formula

Select the cell in your gradebook where you want the letter grade to appear. Type the formula: =XLOOKUP([@Percent],tblGrades[Percent],tblGrades[Grade],,-1).

Review the Match Mode Argument

Ensure the fifth argument is set to -1. This guarantees that a score like 85% will match the 80% threshold (a 'B') rather than returning an error.

Use XLOOKUP to Find Exact or Next Smaller Values

Alternative Option: The traditional LOOKUP function also works seamlessly if your table is strictly sorted. You can use =LOOKUP([@Percent],tblGrades[Percent],tblGrades[Grade]) as a quick alternative.

Solution 2

Use VLOOKUP for Approximate Matching

For older versions of Excel that do not support XLOOKUP, VLOOKUP with the TRUE parameter is the standard method for calculating grades based on brackets.

Advanced Spreadsheet Functions

Calculate Letter Grades Automatically in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP, VLOOKUP, and LOOKUP, making it incredibly easy to create dynamic gradebooks. Enjoy a familiar interface and seamless compatibility with all your Microsoft Excel files.

1. Open your Gradebook: Launch WPS Spreadsheet and open your existing gradebook or start a new document.
2. Prepare the Grading Scale: Set up your threshold table with minimum percentages and letter grades, ensuring they are sorted in ascending order.
3. Apply the Lookup Formula: Click the target cell and enter your preferred formula, such as =XLOOKUP(C5, $F$2:$F$6, $G$2:$G$6, , -1).
4. Fill Down the Column: Hover over the bottom-right corner of the cell and drag the fill handle down to apply the formula to all students automatically.

Native support for XLOOKUP, VLOOKUP, and other advanced grading formulas.Complete compatibility with Microsoft Excel (.xlsx) file formats.Free, lightweight, and user-friendly interface tailored for teachers and students.Built-in templates for gradebooks and educational tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error for some grades?

This usually happens if your lookup table's percentage column is not sorted in ascending order, or if the student's score is lower than the lowest value in your lookup table. Ensure the smallest percentage starts at 0% (or your minimum possible score) at the top of the list.

Can I use XLOOKUP if my grade table is sorted from highest to lowest?

While XLOOKUP can process data from last-to-first using different search modes, for mapping percentage brackets to letter grades (finding the next smaller item), it is highly recommended to keep your thresholds sorted ascending and use the '-1' match mode for accurate calculations.

What is the difference between TRUE and FALSE at the end of a VLOOKUP formula?

The 'FALSE' argument forces VLOOKUP to find an exact match (e.g., looking for exactly 85%). The 'TRUE' argument enables approximate matching, meaning it finds the largest value that is less than or equal to your lookup value. For a gradebook, you must use 'TRUE' because student scores rarely match your threshold exact numbers.

Excel & Data Problems