For day 9 in the 30XL30D challenge, we'll examine the VLOOKUP function. As you can guess by its name, this is one of the Lookup functions, and works with items that are in a Vertical list.
Other functions might do a better job of pulling data from a table (see VLOOKUP traps section below), but VLOOKUP is the lookup function that people try first. Some people get the hang of it right away, and others struggle to make it work. Yes, this function has some flaws, but once you understand how it works, you'll be ready to move on to some of the other lookup options.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let's take a look at the VLOOKUP information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!
Function 09: VLOOKUP
The VLOOKUP function looks for a value in the first column in a table, and returns another value from the same row in that table.
How Could You Use VLOOKUP?
The VLOOKUP function can find exact matches in the lookup column, or the closest match, so it can:
- Find the price of a selected product
- Convert student percentages to letter grades
The VLOOKUP function has the following syntax:
- lookup_value: the value that you want to look for -- it can be a value, or a cell reference.
- table_array: the lookup table -- this can be a range reference or a range name, with 2 or more columns.
- col_index_num: the column that has the value you want returned, based on the column number within the table.
- [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value column sorted in ascending order..
VLOOKUP can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first column, in ascending order, and use an approximate match. You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table's first column (see example 3 below).
Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient. We'll look at those functions later in the challenge, and see how flexible and powerful they are.
Example 1: Find the Price for a Selected Item
The VLOOKUP function looks for a value in the left column of the lookup table. In this example, we'll find the price for a selected product. It's important to get the correct price, so the following settings are used:
- a product name is entered in cell B7
- the pricing lookup table has two columns, and is in range B3:C5
- price is in column 2 of the table.
- FALSE is used in the last argument, to find an exact match for the lookup value.
The formula in cell C7 is:
If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is #N/A
Example 2: Convert Percentages to Letter Grades
Usually, an exact match is required when using VLOOKUP, but sometimes an approximate match works better. For example, when converting student percentages to letter grades, you wouldn't want to type every possible percentage in the lookup table. Instead, you could enter the lowest percentage for each letter grade, and then use VLOOKUP with an approximate match. In this example:
- a percentage is entered in cell C9
- the percentage lookup table has two columns, and is in range C3:D7
- the lookup table is sorted by the percentage column, in ascending order
- letter grade is in column 2 of the table.
- TRUE is used in the last argument, to find an approximate match for the lookup value.
The formula in cell D9 is:
If the percentage is not found in the first column of the lookup table, the VLOOKUP formula result is the next largest value that is less than lookup_value. The lookup value in this example is 77. That value is not in the percentage column, so the value for 75 (B) is returned.
Example 3: Find Exact Price With Approximate Match
The VLOOKUP function can be slow when doing an exact match for a text string. In this example, we'll find the price for a selected product, without using the Exact Match setting. To prevent incorrect results:
- the lookup table is sorted by the first column, in ascending order
- COUNTIF checks for the value, to prevent incorrect results
The formula in cell C7 is:
If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is 0.
Download the VLOOKUP Function File
To see the formulas used in today's examples, you can download the VLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.
More VLOOKUP Info and Examples
- For more VLOOKUP examples, you can visit the VLOOKUP page on the Contextures website.
- Check out Chandoo's blog, where he had an entire VLOOKUP week, with tips and examples.
- For suggestions on speeding up a lookup formula, see Charles Williams' page on Optimizing Lookups.
Watch the VLOOKUP Videos
To see a demonstration of the examples in the VLOOKUP function sample workbook, you can watch these 2 short Excel video tutorials.
YouTube link: Find Product Price with VLOOKUP Function
You Tube Link: Convert Percentages to Letter Grades With VLOOKUP