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)
Post a Comment Blogger Facebook
We welcome comments that add value to the discussion. We attempt to block comments that use offensive language or appear to be spam, and our editors frequently review the comments to ensure they are appropriate. As the comments are written and submitted by visitors of The Sheen Blog, they in no way represent the opinion of The Sheen Blog. Let's work together to keep the conversation civil.