In this article:

Use VLOOKUP to Convert Numerical Scores to Letter Scores

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

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!