In this article:

Use VLOOKUP to Convert Numerical Scores to Letter Scores

May 8, 2024

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:


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

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->