قالب وردپرس درنا توس
Home / Tips and Tricks / How to Use VLOOKUP for a range of values

How to Use VLOOKUP for a range of values



  Excel Logo

VLOOKUP is one of Excel's most popular features. Typically, you use it to find exact matches, such as the ID of products or customers. However, this article explains how to use VLOOKUP with a number of values.

Example 1
: Using VLOOKUP to assign notes to exam grades

For example, suppose we have a list of exam grades, and we want to assign a grade to each grade. In our table, column A shows the actual exam results, and column B shows the character classes we calculated. We also created a table to the right (the D and E columns) that shows the required score to reach each letter.

  Score Evaluation Sample Data

For VLOOKUP, we can use the range values ​​in the column D to map the letter degrees in column E to all actual test results.

The VLOOKUP Formula

Before we apply the formula to our example, let's remember the VLOOKUP syntax:

  = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) 

Work in this formula the variables are as follows:

  • Lookup_value: This is the value you are looking for. For us, this is the rating in column A, beginning with cell A2.
  • table_array: This is often referred to unofficially as a lookup table. For us, this is the table of ratings and associated scores (range D2: E7).
  • col_index_num: This is the column number in which the results are 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 logical value question, so the answer is either true or false. Do you perform a range search? For us, the answer is "yes" (or "TRUE" in VLOOKUP terms).

The full formula for our example is shown below:

  = VLOOKUP (A2, $ D $ 2: $ E $ 7.2, TRUE) 

 Results of our VLOOKUP

The spreadsheet array was adjusted so that it no longer changes when the formula is copied to the cells in column B.

Something about

Caution when looking at areas VLOOKUP requires the first column of the table array (column D in this scenario) to be sorted in ascending order. The formula relies on this order to bring the lookup value into the correct range.

Below is a sample of the results we would get if we sort the table field by the letter letter and not by the score] Incorrect results because the table is out of order ” width=”300″ height=”187″ src=”/pagespeed_static/1.JiBnMqyl6S.gif” onload=”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);” onerror=”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);”/>

It is important to know that the order is important only for range queries. If you specify the value False at the end of a VLOOKUP function, the order is not so important.

Example 2: Providing a discount based on a customer's spend

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

A lookup table (columns D and E) contains the discounts for each output class.

 Second VLOOKUP sample data

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

  = VLOOKUP (A2, $ D $ 2: $ E $ 7.2, TRUE) 

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

You will often find that Excel users write complicated formulas for this kind of conditional logic, but this VLOOKUP provides a short way to accomplish this.

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

  = A2-A2 * VLOOKUP (A2, $ D $ 2: $ E $ 7.2, TRUE) 

 VLOOKUP Returns Conditional Discounts


VLOOKUP is not only useful when You search for specific records such as employees and products. It's more versatile than many people know, and it's an example of how it returns from a set of values. You can also use it as an alternative to otherwise complicated formulas.


Source link