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.

Estimation Data Sample

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), 

Results of our VLOOKUP

The table array has been fixed to stop it from changing when copying the formula over the cells of column B.

Something to be careful about

When viewing ranges with VLOOKUP, it is important that the first column of the table array (column D in this scenario) is sorted in ascending order. The formula relies on this order to place the value you’re looking for in the correct range.

Below is an image of the results we would get if we sorted the array of tables by grade letters instead of scores.

Incorrect results because the table is out of order

It is important to understand that order is only important when searching by range. When you put False at the end of the VLOOKUP function, the order is not that important.

Example two: providing a discount based on how much a customer spends

In this example, we have some sales data. We would like to provide a discount on the amount of sales, and the percentage of this discount depends on the amount spent.

The lookup table (columns D and E) lists the discounts in each expense group.

Second example of VLOOKUP data

The VLOOKUP formula below can be used to return the correct discount from the table.

  = ВПР (А2, $ D $ 2: $ E $ 7,2, TRUE), 

This example is interesting because we can use it in the formula to subtract the discount.

You will often see Excel users writing complex formulas for this type of conditional logic, but this VLOOKUP provides a concise way to achieve this.

Below VLOOKUP is added to the formula to subtract the discount returned from the sales amount in column A.

  = А2-А2 * ВПР (А2, $ D $ 2: $ E $ 7,2, TRUE), 

VLOOKUP return of conditional discounts

VLOOKUP is useful for more than just looking up specific records such as employees and products. It’s more versatile than many people know, and returning from a range of values ​​is an example of this. You can also use it as an alternative to complex formulas.

Похожие записи