Convert Percentages to Letter Grades With VLOOKUP

Usually when I use VLOOKUP, I want to pull information for an exact match. For example, if I enter a customer number in one cell, I want the customer name in the adjacent cell. I don't want the name of a customer whose number is CLOSE to the one that I entered.

In some situations though, an approximate match is preferred, so several values will return the same result. For example, when working with student grades, I don't want to create a lookup table where I have to enter every possible percentage. Instead, my lookup table should have the starting percentage for each grade. Based on the table below, all percentages of 85 or over should receive an A grade.


The Lookup Table

In this example, the lookup table is created on a sheet named Grades. To create the lookup table:

  1. In the first column, enter the minimum percentage for each grade.
  2. In the second column, enter the matching Grade for each percentage.
  3. Sort the percentages in Ascending order. If not sorted A-Z, the results of the VLOOKUP formula might be wrong.

The Report Card

The scores are entered on a sheet named Report Card, where a VLOOKUP formula calculates the grade.

  1. On the Report Card sheet, in cell B4, enter the percentage -- 77.
  2. In cell C4, enter the VLOOKUP formula:
  3. Press the Enter key, and the grade for English -- B -- is returned.
  4. Copy the formula down to the remaining cells in the report card.

How It Works

  1. In the VLOOKUP formula, the first argument, B4, is the value that we want to look up in the Grades table.
  2. The second argument, Grades!$B$4:$C$8, is the location of the lookup table. The reference is absolute $B$4:$C$8, instead of relative, so that the VLOOKUP formula can be copied to other cells, and the table reference won't change.
  3. The third argument, 2, is the column number in the lookup table, where the Grades are located. When a percentage match is found in the first column, a letter grade from the 2nd column will be returned.
  4. The fourth argument, TRUE, means that an approximate match is okay. This is the default setting, and if you omit TRUE, you'll get the same result from the formula.

Watch the Video

If you'd like to see the steps in action, you can watch the short video below.


You may also like...

11 Responses

  1. sam says:

    Always use Index/Match
    That way the Marks can be sorted both Ascending or Descending

  2. Jason Morin says:


    Even with the INDEX/MATCH approach, the array has to be sorted ascending or descending in order to return a value when the lookup_value is not found.


  3. dan says:

    does not work

  4. Steven says:

    Awesome tutorial. I teach high school students and i always use excel to do the calculations of their grades. Keep it up!

  5. Fifie says:

    I made it! thanks for the tutorial!

  6. eLLY says:

    yes, its work, thanks…i finished my work today is about only 1 hours for 25 classes…tq so much!!

  7. Catherine says:

    Super helpful! Thank you SO much!

  8. owino says:

    I have 64 students doing 11 subjects and I need to generate report card for them automatically. they are all in one sheet. how can I do it

  9. Mario says:

    In case anyone has trouble with this, it might be because your results sheet has a % on it and not a whole number. Either fill the lookup table with % numbers or change the results sheet.

  1. August 6, 2015

    […] LOOKUP: The LOOKUP function allowed me to create a grade scale and then transform the percentages into letter grades. It took a little finagling to get it to work (the trick is to sort in ascending order in the percentages column), but it’s fantastic to see the formula switch things over automatically as the grades are adjusted. […]

Leave a Reply

Your email address will not be published. Required fields are marked *