VLOOKUP is one of the most famous Excel functions. Typically you’ll use it to look for exact matches such as product or customer IDs, but in this article we’ll look at how to use VLOOKUP with a range of values.
Example One: Using VLOOKUP to Assign Letter Scores to Exam Scores
As an example, let’s say we have a list of exam results 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 calculated letter grades. We also created a table on the right (columns D and E) that shows the scores required to achieve each letter grade.
With VLOOKUP, we can use the range values in column D to assign letter grades in column E to all of the actual exam grades.
Formula VLUKUP
Before we move on to applying the formula in our example, let’s briefly recap the VLOOKUP syntax:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
In this formula, the variables work like this:
- lookup_value: This is the value you are looking for. For us, this is the score in column A, starting in cell A2.
- table_array: This is often referred to as the informal lookup table. For us, it is a table containing scores and corresponding scores (range D2:E7).
- col_index_num: This is the number of the column where the results will be placed. In our example, this is column B, but since the VLOOKUP command requires a number, this is column 2.
- range_lookup> This is a boolean question, so the answer is either true or false. Are you doing a range search? For us, the answer is yes (or «TRUE» in VLOOKUP terms).
The completed formula for our example is shown below:
= ВПР (А2, $ D $ 2: $ E $ 7,2, TRUE),
The table array has been fixed to stop it from changing when copying the formula over the cells of column B.
