Use VLOOKUP to Convert Numerical Scores to Letter Scores

Learn an easy and valuable strategy for mastering Google Sheets which will allow you to convert numerical scores into letter grades or assign any one value to another.

Table of Contents
  1. Using VLOOKUP for Value Conversion
  2. Sample Sheet
Table of contents
Chapter 1
Chapter 2
Chapter 3
  1. Using VLOOKUP for Value Conversion
  2. Sample Sheet

Recently I found myself having to convert hundreds of scores to numerical grades. I initially thought of using a long, nested series of IF formulas and using the SWITCH function that we recently tackled, but I discovered a simpler method.

I just recently discovered this, and I want to share it with you.

Using VLOOKUP for Value Conversion

But first, our sample:

Sample grades
Sample grades

And, of course, our grade conversion scheme:

Conversion scheme
Conversion scheme

For this trick to work, you need to list down the conversion from the lowest value to the highest value.

As mentioned in the title of this short tutorial, we will use VLOOKUP. For this trick, we will use the following syntax of VLOOKUP:

=VLOOKUP(cell_containing_the_score, range_of_the_conversion_table, column_containing_the_equivalent)

Where the following are defined as follows:

cell_containing_the_score - points to the cell containing the score. For our example above, it’s from B2 to B13. 

range_of_the_conversion_table - the range of the conversion table. For our example above, that is E2:F8.

column_containing_the_equivalent - the column number within the range that contains the conversion table. For our example, that is on the 2nd column, so the value is 2. 

So for our first entry, the specific formula would be:

=vlookup(B2,$E$2:$F$8,2)

The range includes the character $ so that it won’t adjust when I copy it to the other cells in the same column. 

You can now see the result for yourself:

Converted grades.
Converted grades.

Sample Sheet

VLOOKUP Convert Grades Sample Sheet

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

July 17, 2021

Use VLOOKUP to Convert Numerical Scores to Letter Scores

Google Sheet spreadsheet

Recently I found myself having to convert hundreds of scores to numerical grades. I initially thought of using a long, nested series of IF formulas and using the SWITCH function that we recently tackled, but I discovered a simpler method.

I just recently discovered this, and I want to share it with you.

Using VLOOKUP for Value Conversion

But first, our sample:

Sample grades
Sample grades

And, of course, our grade conversion scheme:

Conversion scheme
Conversion scheme

For this trick to work, you need to list down the conversion from the lowest value to the highest value.

As mentioned in the title of this short tutorial, we will use VLOOKUP. For this trick, we will use the following syntax of VLOOKUP:

=VLOOKUP(cell_containing_the_score, range_of_the_conversion_table, column_containing_the_equivalent)

Where the following are defined as follows:

cell_containing_the_score - points to the cell containing the score. For our example above, it’s from B2 to B13. 

range_of_the_conversion_table - the range of the conversion table. For our example above, that is E2:F8.

column_containing_the_equivalent - the column number within the range that contains the conversion table. For our example, that is on the 2nd column, so the value is 2. 

So for our first entry, the specific formula would be:

=vlookup(B2,$E$2:$F$8,2)

The range includes the character $ so that it won’t adjust when I copy it to the other cells in the same column. 

You can now see the result for yourself:

Converted grades.
Converted grades.

Sample Sheet

VLOOKUP Convert Grades Sample Sheet

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.