One of the many functions that I am constantly asked about us vlookup in Google Sheets. This powerful little function can be used in many ways and in this lesson I will show you one of the most common ways I use it each day.
SCENARIO
The scenario is that you have a list of students and a list of percentage test results. You have input the test results but want to automatically assign a grade based on the percentage.
| A | B | C | |
| 1 | STUDENT | TEST SCORE % | GRADE |
|---|---|---|---|
| 2 | Green, Sam | 46 | |
| 3 | Jones, Peter | 95 | |
| 4 | Smith, Claire | 72 |
What we need to do now is to automatically populate the GRADE column using a vlookup.
To do this I usually create a second tab on the Google Sheet and call it ‘Lookups’.
The table will contain the test score boundaries and grades as follows:
| A | B | |
| 1 | TEST SCORE % | GRADE |
|---|---|---|
| 2 | 0 | E |
| 3 | 25 | D |
| 4 | 45 | C |
| 5 | 65 | B |
| 6 | 85 | A |
The formula that would be placed in cell C1 would be:
=vlookup(B1, Lookups!A2:B6, 2)
There are 3 parameters. The first is the cell that contains the value we want to lookup. The second os the location of the lookup table. In this case it is the Lookups tab and cells A2 to B6. We then specify which value we wish to return. In this case it is the value in the second column.
The full table with formulas will look something like this:
| A | B | C | |
| 1 | STUDENT | TEST SCORE % | GRADE |
|---|---|---|---|
| 2 | Green, Sam | 46 | =vlookup(B2, Lookups!B$2:C$6, 2) |
| 3 | Jones, Peter | 95 | =vlookup(B3, Lookups!B$2:C$6, 2) |
| 4 | Smith, Claire | 72 | =vlookup(B4, Lookups!B$2:C$6, 2) |
HAS THIS ARTICLE BEEN USEFUL?
Has this article been useful? Then consider making a small donation now to help support this site. Your donation will go towards the running costs associated with the site. You can have your name listed as a page sponsor, if you wish!
The full table with grades assigned will look something like this:
| A | B | C | |
| 1 | STUDENT | TEST SCORE % | GRADE |
|---|---|---|---|
| 2 | Green, Sam | 46 | C |
| 3 | Jones, Peter | 95 | A |
| 4 | Smith, Claire | 72 | B |
