VLOOKUP is one of Excel’s most well-known functions. You’ll typically use it to look up exact matches, such as the ID of products or customers, but in this article, we’ll explore how to use VLOOKUP with a range of values.
Example One: Using VLOOKUP to Assign Letter Grades to Exam Scores
As an example, say we have a list of exam scores, and we want to assign a grade to each score. In our table, column A shows the actual exam scores and column B will be used to show the letter grades we calculate. We’ve also created a table off to the right (the D and E columns) that show the score necessary to achieve each letter grade.
With VLOOKUP, we can use the range values in column D to assign the letter grades in column E to all the actual exam scores.
The VLOOKUP Formula
Before we get into applying the formula to our example, let’s have a quick reminder of the VLOOKUP syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
In that formula, the variables work like this:
- lookup_value: This is the value for which you are looking. For us, this is the score in column A, starting with cell A2.
- table_array: This is often referred to unofficially as the lookup table. For us, this is the table containing the scores and associated grades ( range D2:E7).
- col_index_num: This is the column number where the results will be placed. In our example, this is column B, but since the VLOOKUP command requires a number, it’s column 2.
- range_lookup> This is a logical value question, so the answer is either true or false. Are you performing a range lookup? For us, the answer is yes (or “TRUE” in VLOOKUP terms).
The completed formula for our example is shown below:
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
