Vlookup

The purpose of the Vlookup function is to bring related data from another table into the current cell.  Vlookup stands for “Vertical Lookup”.  It is used to “lookup” information from another worksheet.

If you would like, feel free to check out our two minute video overview of vlookup.

The Vlookup function is one of my top 5 favorite functions, and can be leveraged to automate many tasks we perform.  It has a wide variety of uses, including seeing if the same value exists in two different worksheets, combining data from two different worksheets, pulling in account names from the chart of accounts, pulling values from Quickbooks reports that have been exported to Excel, and more. 

The Vlookup() function has 4 function arguments and has the following syntax:
=Vlookup(lookup_value, Table_array, Col_index_num, [range_lookup])

WHERE
·    lookup_value is the value in the current table to lookup, for example department number
·    Table_array is the table that contains all of the values, for example the department number and name pairs.  It is assumed the values to match are in the left-most column of this range.
·    Col_index_num is the column number that has the data you wish to return when a match is found, for example, “2” means the second column
·    [range_lookup] controls the behavior when no match is found.  FALSE (or 0) returns an error message when no match is found.  TRUE returns results for the closest value that is less than the lookup_value. Note: the underlying table_array must be sorted in ascending order when this argument is TRUE, and can be unsorted when this argument is FALSE (or 0).  This argument is optional, and when not declared defaults to TRUE.

Note: any function argument entered between [square brackets] is optional.  The brackets mean optional.

Department name lookup
Many times we receive raw data from an accounting system or other database.  From that raw data, we need to generate summary reports.  Sometimes, we may receive raw data that does not contain all of the fields we need for our report.  For example, we may receive a report that shows department number and monthly expense, but the data does not include department name.  In our final report however, we need department name.  Rather than type in the department names, we use Vlookup.  Often, a simple copy and paste of the department names would not work because the order of the departments may differ, and certain departments may be excluded. 

In this example, we will bring the department name from the department listing into our expense report.

The department listing is shown below.

Please note that we have used Department names as our example, but this feature is useful with any data that has number/name pairs.  It seems as if most data we encounter have number/name pairs.  Account number/account name, item number/item name, employee number/employee name, vendor number/vendor name, and so on.  

The expense report is shown below.

Our objective is to get the department names into column B.  The Vlookup formula we write in cell B5 asks Excel to find the department number 100 (cell A5) in the department listing and to return the department name if it finds a matching department number.  We instruct Excel to perform this by using the following formula logic:
    =Vlookup(find the department number, in the department listing, return the department name, exact match)

The actual formula shown used in Figure 108 is:
 =VLOOKUP(A5,Source!$A$4:$B$8,2,0)
Where:
·    A5 represents the department number to find
·    Source!$A$4:$B$8 represents the department listing on the Source worksheet as shown in Figure 106
·    2 represents the second column in the range, and tells Excel that if it finds a matching department number to return the corresponding department name (the 1st column is department number, 2nd column is department name)
·    0 represents “exact match only” logic and tells Excel to return an error code if it does not find a matching department number

Note the use of absolute cell references for the lookup range (by including dollars signs: Source!$A$4:$B$8) so when we fill the formula down from B5 to B7, we will get the correct department names as shown below.

You can imagine that this same technique can be used when looking up account names in the chart of accounts, or item names in the item list, or vendor names in the vendor list, or customer balances in the customer aging, or employee addresses from the employee list, or percent-complete from the jobs listing, or number of shares in the stock disposition listing, or share price in the historical stock quote listing, or number of options in the stock option list, or quantity on hand from the inventory list, and so on.  This technique has wide ranging applications.

See where value falls between a range
This example digs into the 4th argument of the Vlookup function, the Range_lookup.  In the previous example, we instructed Excel to only return a value if there was an exact match.  That is, to return an error code if there was not a match.  Now, we will instruct Excel to return a value even if there is not an exact match.  This is very useful when determining where a value falls within a range.

Conceptually, the 3rd function argument tells Excel what to do when it finds a matching value in the first column of the lookup range.  The 4th argument tells Excel what to do if there is no match.  The 4th argument is a “Boolean” argument, which means it can be only one of two values.  It can be “true” or “false”. If the argument is true, Excel will return the approximate match (the largest value that is less than the lookukp value).  If the argument is false, the vlookup function will return an error code.  True can be expressed in many different ways.  It can be expressed by typing “TRUE”, it can be expressed by any number other than 0, or it can be expressed by any function that returns a true value (like the And() or Or() functions).  In addition, the 4th argument is OPTIONAL, which means that if not entered, it will default to True.  False can be expressed in several ways.  False can be expressed by typing “FALSE”, or by the number 0, or by any function that returns a false value.

For example, let’s say your company pays a royalty based on sales volume.  If sales of a specific product are less than $100,000 the royalty rate is 10%.  Between $100,000 and $500,000 the royalty rate is 5%.  Above $500,000, the royalty rate is 2%.  We may have a spreadsheet as shown below.

Note the formula  =VLOOKUP(B4,A9:C11,3).  Since we did not specify a Range_lookup argument (the 4th argument), the argument defaults to “true” and Excel finds the value that is the closest but less than the value.  Note that the range must be in ascending order.  Also note that the Vlookup formula completely ignores any data in the B column, the “To” column, since it performs its lookup on the values in the leftmost column of the lookup range.  The “To” column is simply included above since it facilitates human review and understanding.

When sales are updated to $350,000, the Vlookup function instantly updates and reflects the proper rate (5%), as shown.

Please note that the Vlookup function simply returns the Rate, which is displayed in cell B5.  The Vlookup function does not compute the payment amount (sales * rate).  The example above is designed to illustrate how the Vlookup can return a value within a range to get us warmed up.  The worksheet example above does not apply 10% to the first $99,999 of sales, and then 5% to the next layer; it applies the same rate to all sales.  So, now that we are getting warmed up, we’ll need to add additional formulas in order to apply the rate for each layer to that layer.  To illustrate this, we’ll use a familiar example of taxable income.

In the screenshot below, you will see an income tax table. 

We would like to enter the Taxable Income in cell B4, and have the formulas properly compute the Tax by using the table that begins in row 9.  We can pull back the Marginal Tax Rate to cell B5 by using the Vlookup formula.  The challenging formula is in B6 which computes the Tax.  Before we look at the formula in detail, we need to talk conceptually for a bit.

Conceptually, we compute the Tax by adding two numbers together.  We add the cumulative tax from all previous layers (found in column D) and the tax for the current layer.   

The Vlookup formula for the Marginal tax rate cell is shown below.

The formula to return the tax Rate from the lookup table for the given Taxable Income as displayed in cell B5 is:
=VLOOKUP(B4,A10:D15,3)
Where:
·    B4 is the Taxable Income to lookup in the tax table
·    A10:D15 is the rate table (lookup range)
·    3 is the column number that has the value to return (the Rate)
·    The 4th optional argument is not defined, thus it inherits the default value of “true”, which tells Excel that when an exact match is not found to return the Rate for the largest taxable income that is less than the lookup value in B4 (which is this case is $7,001)

The formula for the Tax cell is shown below.

The formula that computes the Tax in cell B6 is:
=(B4-VLOOKUP(B4,A10:D15,1)-1)*B5+VLOOKUP(B4,A10:D15,4)

By examining the formula, you will notice that it is simply the addition of two items.  The second part of the formula (after the +) is “VLOOKUP(B4,A10:D15,4)” and returns the cumulative tax of all previous layers.  The first part of the formula (before the +) is “(B4-VLOOKUP(B4,A10:D15,1)-1)*B5” which takes B4 (the Taxable Income) and subtracts from it the amount of Income that has already been included in the cumulative tax computations, and then multiplies the result by the Marginal tax rate found in cell B5.  Computing the amount of Income that has already been included in the cumulative tax computations is performed with the “VLOOKUP(B4,A10:D15,1)-1”, which has two components: the VLOOKUP() component which returns the largest value less than the lookup value in this case $7,001 and the “-1” which subtracts 1 from that result since the previous layer only includes $7,000 of taxable income.

If you would like to practice the vlookup() function, there are some workbooks you can download below.

AttachmentSize
vlookup.xls280.5 KB
vlookup_answers.xls301 KB

Vlookup