Retrieval Functions Part I - VLOOKUP

Some of the most useful functions in Excel belong to the category of functions known as Lookup & Reference. At first glance this category of functions may seem intimidating or not relevant to a novice Excel user. However, looks can be deceiving as a few of these functions can be used in countless scenarios and can save you hours of time. In this post I will focus on the VLOOKUP function.

Purpose

The "V" in VLOOKUP stands for vertical. The purpose of the function is to take a value, look for it in the leftmost (vertical) column of a table, and retrieve a value from a specified column in the same row. For example, if you type EMP-0045 into a cell then a vlookup function could be used to find that value in the leftmost column of the table below and retrieve the value in either column 2, 3, or 4; the FirstName, LastName or Salary.

Other practical uses for this function may be to retrieve a tax rate based on an employee's salary,  a product description based on a product ID, a unit cost amount based on the number of units being ordered, etc.

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

lookup_value

This argument is most commonly a reference to a cell that contains a known value that you want to look up.

table_array

This argument will consist of a range of cells, expanding multiple columns and rows. The important thing to note is that the leftmost column in the range of cells you select must include the lookup_value. Given the example above, the table_array would be the range A1:D4.

col_index_num

This argument will be the number of the column that contains the value you want the function to retrieve. So if you want to return the FirstName of an employee based on the lookup_value of EMP-0045 then the col_index_num would be 2 because the FirstName is the second column of the table_array. If you wanted to retrieve the salary then this argument would be 4. It is important to note that the col_index_num is always within the context of the range of cells selected as the table_array. So it would not matter if the table in this example was located in columns D through G in the Excel worksheet, there are still columns 1, 2, 3, and 4.

range_lookup

This argument usually creates the most confusion as it is less intuitive than the others. This argument can only be one of two values: TRUE or FALSE. A FALSE value means that Excel must find an exact match to the lookup_value in the leftmost column of the table. If your lookup_value is text then you would, without a doubt, want Excel to find an exact match to your value in order to ensure you retrieve the correct value. A TRUE value is necessary when the values in the leftmost column of your table represent a range of numbers and you want Excel to find an approximate match to the lookup_value.

Let's say you have a table similar to the one below that outlines the various commission rates your sales reps can earn based on the amount of revenue generated by a sale.

In this scenario your lookup_value could be any amount. You would want Excel to find an approximate match to that value in the leftmost column of the table to then return the commission rate value in column 2. So if the revenue generated from a sale was  $25,440, then the commission earned should be 3.0% because that value falls between $15,000 and $35,000. If the value for this argument were FALSE then Excel would return a #N/A error because there is no exact match available.

Demonstration