Excel Tip -- Vlookup()

An Excel function worth knowing is Vlookup(). It allows you to pull in information from other worksheets. For more information, read the full text...

Vlookup

Purpose

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.

Useful When

The Vlookup is useful when you have data that exists in another worksheet that you need to bring into the current worksheet. For example, you have department numbers in the current spreadsheet, and you have the department number and names in another worksheet. You want to bring in the department names into the current worksheet.

Detail

The Vlookup function is designed as follows: =Vlookup(Value to Lookup, Lookup Range, Return Value from Column Number, Match Type) WHERE
* Value to lookup is the value in the current table to lookup, for example department number
* Lookup Range is the table that contains all of the values
* Return Value from Column Number is the column number that has the data you wish to return, for example 2 for the second column
* Match Type is usually 0, for exact match.

Exercise

We will use two exercises to demonstrate Vlookup. 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 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. In addition, many times we cannot simply copy and paste the department names from another source because the order of the departments may be different, and certain departments may be excluded. In this exercise, we will bring the department name in from the department listing into our expense report.

The department listing is shown in Figure 60.


Figure 60

The expense report is shown in Figure 61.


Figure 61

Our objective is to get the department names into column B without typing or pasting. The logic behind the Vlookup formula we are about to write in cell B5 is: Lookup the department number 100 in the department listing and return the department name if there is an exact match of department numbers. We instruct Excel to perform this by using the following formula logic: Vlookup(department number, in the department listing, department name, exact match) The actual formula is shown in Figure 62.


Figure 62

You can see the formula above, where:
* A5 represents the department number
* “Source!A4:B8” represents the department listing on the Source worksheet as shown in Figure 60
* 2 represents the second column in the range (1st column is department number, 2nd column is department name)
* 0 represents “exact match only” logic

If we use absolute references for the lookup range and fill the formula down from B5 to B7, we will get the correct department names as shown in Figure 63.


Figure 63

You can imagine that this same technique can be used with looking up account names in the chart of accounts as well. This technique has wide ranging applications.

Excel Tip -- Vlookup()