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:
And, of course, our grade 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:
Sample Sheet
Recommended articles
More resources
