Using VLOOKUP in Google Sheets

The VLOOKUP function in Google Sheets enables you to look up data in a table (e.g. a range in your spreadsheet) based on a key.

For example, suppose you have a range of students’ exam results, measured as percentages, and you want to grade those students with a letter grade. Here are the exam results:

Click image to enlarge

Click image to enlarge

In another location (here it’s on the same sheet, but it doesn’t have to be) we list our choice of grade definitions:

Click image to enlarge

Click image to enlarge

Now let’s employ VLOOKUP. The general syntax is:

VLOOKUP(search_key, range, index, [is_sorted])

Here, search_key is the list of student exam results, listed in column B. For each student, we’ll look up their exam score as a key in range (here F2:G7), and return the value in column G (the second column in range, so in our expression the parameter index equals 2). Since F2:F7 is sorted in ascending order, we’ll use TRUE for the optional is_sorted parameter, which allows VLOOKUP to return a best-match (less than or equal to the search key), rather than exact-match, response. Here’s the code for cell C2:

=VLOOKUP(B2, $F$2:$G$7, 2, TRUE)

Notice the dollar signs on the definition of the lookup range; we’re using absolute values, because we want this range to stay unaltered when we copy and paste the expression from cell C2 to the other cells in column C.

Finally, here’s the sheet with the grades looked up:

Click image to enlarge

Click image to enlarge