Skip to content

Use VLOOKUP to Assign Letter Grades

This article will show you how to use the VLOOKUP function to assign letter grades based on the given numerical score for each student.

If this sounds interesting to you, then read on!

The VLOOKUP Function

The VLOOKUP function is commonly used to look up a piece of data that is related to some other piece of data.

For example, let’s say you have the following professions and the average salary for each.

You could use the VLOOKUP function to look up the salary for a given profession.

In an example like this, the VLOOKUP function is looking for an exact match. In other words, it is looking to find a specific profession and then provide the salary for that profession.

What you may not know is that the VLOOKUP function can be used to search for approximate matches as well.

Have VLOOKUP Search for an Approximate Match

To get the VLOOKUP function to search for an approximate match (rather than an exact match), you must NOT set the fourth argument to FALSE.

=VLOOKUP(lookup_value, lookup_range, column_num, search_type)

The fourth argument in VLOOKUP determines what type of match you want the function to perform. This argument can be TRUE or FALSE.

  • TRUE – The VLOOKUP function will search for an approximate match. This is the default value.
  • FALSE – The VLOOKUP function will search for an exact match.

Therefore, if you want the VLOOKUP function to find an approximate match, you can set the fourth argument to TRUE. You can also leave off the fourth argument entirely since it will default to TRUE anyway.

=VLOOKUP(lookup_value, lookup_range, column_num)

Important Note
The leftmost column of the lookup range must be sorted in ascending order for the VLOOKUP function to find an approximate match.

Look Up the Letter Grade

At this point, you know what the VLOOKUP function is and how to set it up to look for an approximate match.

All that’s left is to learn how to use it to look up and assign letter grades.

Take a look at the list of names and scores below.

The goal is to use the VLOOKUP function to assign a letter grade to each person based on their score.

To do this, we need to reference a range that specifies which score thresholds represent which letter grade. Take this range, for instance.

Based on the range above:

  • A student must get at least a 70 to achieve a C
  • At least an 80 is required for a B
  • At least a 90 is required for an A

Using this range of cells, we can use the VLOOKUP function to assign a letter grade based on each student’s score.

Take a look at the following formula.

=VLOOKUP(B2, $E$2:$F$5, 2)

The VLOOKUP function starts by referencing B2 as the lookup value. This is the student’s score.

=VLOOKUP(B2, $E$2:$F$5, 2)

The function then references $E$2:$F$5 as the lookup range. The function will look here to find an approximate match for the student’s score.

=VLOOKUP(B2, $E$2:$F$5, 2)

The reference $E$2:$F$5 is written as an absolute reference so that it will remain the same when the VLOOKUP function is copied down to other cells for the other students.

The VLOOKUP function will search for the largest value that is a less than or equal to the lookup value. (It does this because there is no fourth argument. Therefore, the function will search for an approximate match this way by default.)

Once it does, the VLOOKUP function will look in the second column of the lookup range to retrieve and return the letter grade.

=VLOOKUP(B2, $E$2:$F$5, 2)

Now that the VLOOKUP function has been set up for the first student, it can be copied down (using the fill handle) for all of the other students.

The VLOOKUP function does its thing, and it finds and returns the appropriate letter grade for each student!

This Post Has 2 Comments

  1. I always us the exact match for VLOOKUP, so this was a great example to help me understand a situation where I could use the approximate match. Also, the VLOOKUP function workbook is so cool!!! I would highly recommend!

    1. Meagan,
      I agree with you that vlookup function is really useful in this way. I do reverse the columns having the numbers on the first column and the letters on the second column. I guess that will from the website that I first got the information this was a suggestion. In the implementation, do you find any difference in the results?

      Carmen

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top