30 Excel Functions in 30 Days: 09 – VLOOKUP

Icon30DayFor 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.
Vlookup00

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

VLOOKUP Syntax

The VLOOKUP function has the following syntax:

  • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
    • 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 Traps

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:
=VLOOKUP(B7,B3:C5,2,FALSE)
Vlookup01
If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is #N/A
Vlookup02

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:
=VLOOKUP(C9,C3:D7,2,TRUE)
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.
Vlookup03

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(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)
Vlookup04
If the product name is not found in the first column of the lookup table, the VLOOKUP formula result is 0.
Vlookup05

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

_____________

0 thoughts on “30 Excel Functions in 30 Days: 09 – VLOOKUP”

  1. Vlookup is very usefull. I work in file were i'm not alowed to use macros. There a list of 1500 items and on another sheet i have an invoice of sorts were i can add items to be collected from the stock. Instead of typing the product everytime i use vlookup to type just the item's code and excel goes to the other sheet, compares the code i entered with each product's code and when the correct one appears it copies the description and any other concatenated info i may ask it to copy. It even controls my stock and tells me when the stock is low by using conditional formatting and an if statement. Vlookup can be a time saver!

  2. If COUNTIF returns a number greater than zero, the IF logical_test argument evaluates to TRUE. Not very intuitive, nor well documented, but nevertheless true. (no pun intended)

  3. [...] as you did with VLOOKUP, you can use MATCH to help convert a student's score to a letter grade. In this example, it is [...]

  4. One glass half full one glass have empty – for the one who couldn't use it – call center – what is the extension of Jim Jones? 3456. In the example "I-wiil-Adapt" I like that idea but do you need to retypr the info over again to enter it onto the invoice? A customer asks for item 23345, you type it in Vlookup finds it as a lawn mower and you have 3 in stock, great now how does it know that you want to add it to the invoice if it is a one cell item? Sorry a little slow on this one, but like the idea.

  5. I need help please. I have a spreadsheet I am working on. I need to use a function that can look up a date in a column and find the data that goes with that date. Sounds easy, well I'm just having a heck of a time. Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.