Hi Everyone,
I just got back from a cruise last week to the eastern Caribbean aboard the Ruby Princess. My wife and I had a great time. But now it is time to resume class. I can't believe it is almost halfway done !
VLOOKUP is a database function that can work with lists of things in an Excel worksheet. You may have a worksheet that contains a list of products, customers, or employees and VLOOKUP will allow you to pull that information into your worksheet.
Microsoft has a somewhat dry description of VLOOKUP on their website at:
Here is a quick example of how VLOOKUP can be used to bring values from a list, or database table into an Excel worksheet based upon a unique value. This example uses a list of employees in our company. VLOOKUP needs to have a column with unique values, and that column must be the first column in the table. In our example, we use the Employee Number as our unique identifier because each employee has their own employee number. We created the Employee table on a worksheet called Employee List.
When you use the VLOOKUP function in a cell and pass it one of the unique identifiers from your database table, it will return you one of the other row values associated with that unique identifier. If you pass VLOOKUP an employee number, it would return to you either the employee’s name, department, hire date or position. You can determine which of these pieces of information you want back.
Syntax for the function is =VLOOKUP(A2,'Employee List'!A2:F5,2,FALSE)
- where A2 represents the cell that contains the value you are going to search the table for
- where ‘Employee List’ represents the worksheet containing the Employee table
- where !A2:F5 is the array of cells of data in the Employee table.
- where 2 represents the 2nd column of the table containing the data you want to insert.
- Where FALSE is the value to use if the first column of the table is not sorted in ascending order.
In a new worksheet we will create a new speadsheet that will pull data from the Employee table into the new worksheet into column B (Employee):
To find the VLOOKUP formula, use Formulas, Insert Function to select the Vlookup function for Cell B2
Then use the wizard to insert the values for the four parameters:
Your formula should look like this:
=VLOOKUP(A2,'Employee List'!A2:F5,2,FALSE)
This should produce the following results:
You can have multiple VLOOKUP functions in a worksheet pulling data from various other worksheets and lists. It is a powerful tool for Excel users.
Resources:
There are several videos on Youtube that show how VLOOKUP can be used. Here is one simple example from Rdjalayer:
The website, How-To Geek, has an example of a Product List using product numbers as the unique value and allowing you to pull in the product description and price columns from the list. See http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/
VLOOKUP can also be combined with formulas to produce more complex results. There are examples of this at the techontheweb website at:
Go Gators!
Wayne
No comments:
Post a Comment