VLOOKUP is one of Excel's most famous features. You usually use it to find exact matches, eg. ID for products or customers, but in this article we examine how to use VLOOKUP with a number of values.
Example One: Use VLOOKUP to assign Write grade to exam result
For example, say we have a list of exam, and we want to assign a grade to each score. In our table, column A shows the actual exam results and column B will be used to show the letters we calculate. We have also created a table to the right (D and E columns) that shows the points required to achieve each letter quality.
With VLOOKUP, we can use the interval values in column D to assign the letter grades in column E to all actual test results.
Before embarking on using the formula for our example, let's quickly remind 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 you are looking at. For us, this is the point in column A, which begins with cell A2.
- table_array: This is often called unofficially as the lookup table. For us, this is the table that contains points and associated grades (area 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 is column 2.
- range_lookup> This is a logical value question, so the answer is either true or false. Do you perform an interval search? For us, the answer is yes (or "TRUE" in VLOOKUP terms).
The finished formula for our example is shown below:
= VLOOKUP (A2, $ D $ 2: $ E $ 7.2, TRUE)
The table matrix has become fixed to stop it being changed when the formula is copied down in the columns B.
Something to be careful about
When looking at intervals with VLOOKUP, it is important to sort the first column of the table set (column D in this scenario) in ascending order. The formula depends on this order to place the lookup value at the right interval.
Below is a picture of the results we would get if we sorted the table set with the grade letter instead of the score.
It is important to be clear that the order is only necessary with interval lookups. When you add False to the end of a VLOOKUP function, the order is not that important.
Example two: To give a discount based on how much a customer spends
In this example we have some sales data. We want to give a discount on the sales amount and the percentage of that discount depends on the amount used.
A lookup table (columns D and E) contains the rebates at each expense bracket.
The VLOOKUP formula below can be used to return the correct discount from the table.
= VLOOKUP (A2, $ D $ 2: $ E $ 7.2, SANT)
This example is interesting because we can use it in a formula to subtract the discount.
You will often see Excel users write complicated formulas for this type of conditional logic, but this VLOOKUP provides a brief way to achieve it.
Below, VLOOKUP is added to a formula for subtracting the discount returned from the sales amount in column A.
= A2-A2 * VLOOKUP (A2, $ D $ 2: $ E $ 7.2, TRUE)
VLOOKUP is not only useful for looking for specific items such as employees and products. It is more versatile than many know, and it is an example of getting it back from a number of values. You can also use it as an alternative to otherwise complicated formulas.