How to Use VLOOKUP on a Range of Values

Excel logo

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.

Grade score sample data

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)

Results of our VLOOKUP

Read the remaining 16 paragraphs



from How-To Geek https://www.howtogeek.com/406053/how-to-use-vlookup-on-a-range-of-values/
via IFTTT
loading...