Browse > Home / Business Intelligence, Technology / The VLOOKUP Hookup

The VLOOKUP Hookup

December 22, 2009

Companies invest large amounts of money, time, and other resources acquiring and implementing reporting and analysis software.  I’ve seen organizations invest hundreds of thousands of dollars in projects and fail to realize a decent return on their investments.  The point of this series of posts is to educate you about the reporting and analysis capabilities of a tool your organization probably already owns: Microsoft Excel.

In this series of posts, I will discuss a number of these capabilities and will give some concrete examples of how to utilize them.

I will be using Excel 2007 for these examples.  Much of this functionality is also available in Excel 2005, it’s just not as easy to use and does not have some of the more advanced features.

Getting the Data
The first step in any effort is to get some data into Excel.  We’ll start out using a simple static list.  You probably already use lists like this regularly.  If you don’t utilize Excel in this way today, think of the reports that you work with from the various systems that you run your organization with.  In most cases, you could probably either copy andhttp://thefuturevalueofbusiness.com/wp-admin/post.php?action=edit&post=745&message=6 paste or import these reports into Excel to get some data to work with.

In future posts, we’ll cover a much more powerful method of acquiring data by connecting to external databases from within Excel.  For now though, we’ll stick with this simple example.

I’ll be working with the sample database that comes with Microsoft’s SQL Server database software.  This sample database contains information about a fictitious company called Adventure Works.  Below, you can see that I have an extract of order information that I’ve pasted into Excel.

This is the most common manner in which people utilize Excel for reporting purposes: simple lists of data pasted or imported from other sources.  In most situations, this data comes from existing reports or queries.  My example above is a very simple query…you can see that we don’t even have names or descriptions for most of the data.  For example, Column F is showing us the Product ID instead of the Product Name.

The best way to solve this problem is to have the author of the report or query modify it to include the Product Name in addition to the Product ID.  Let’s imagine that this is not a realistic option though; there is a way that we can solve this problem using an extremely powerful Excel formula called VLOOKUP.

Using VLOOKUP

To expand on our situation above, let’s imagine that I have a second worksheet in my Excel workbook.  I have an image of this second sheet below.

The Product ID in column A corresponds to the Product ID in column F on the Orders List.  We are going to use VLOOKUP to take the Product ID in the orders list and lookup the Product Name in the product list.

To make the formulas a little more understandable, I am going to rename the Sheet with the order list “Orders” and I am going to rename the Sheet with the products list “Products”.

On the “Orders” sheet, let’s insert a column immediately to the right of the Product ID.  We’ll label it “Product Desc” in Row 1.  In Row 2, we’ll enter the VLOOKUP formula:

=VLOOKUP(F2,Products!A:B,2,FALSE)

The parameters (the information between the parentheses) tell Excel how to lookup the value we want:

  • The first parameter, “F2”, tells Excel what value we are performing the lookup for.  In this case, we are looking up the Product ID.
  • The second parameter, “Products!A:B”, tells Excel where to go to do the lookup.  Here I selected the first 2 entire columns on the “Products” sheet.
  • The third parameter, “2”, tells Excel to bring back the data in column 2 from the lookup list when it finds a match for the value from cell F2.  I know that’s a confusing sentence at best, but it will make sense in a moment.
  • The last parameter, “FALSE”, tells Excel that we want it to return only an exact match for the value we are looking for.  If Excel cannot find an exact match for the Product ID, it will return an error indicator.

The Results
Now, let’s take a look at the results of our formula.  The screenshot below shows what I have now.

This screenshot shows a few rows from the “Products” sheet:

Hopefully you can see how VLOOKUP works now.  Excel took the value in F2 in the “Orders” sheet, 776.  It went to the first column of the range we gave it; that range was Columns A and B of the “Products” sheet.  It scanned through that range until it found a match for 776.  It then took the value in the 2nd column of the range, column B, in the same row and returned the value in that cell (“Mountain-100 Black, 42”).

One thing I didn’t make clear before that I want to point out now.  VLOOKUP is always going to look in the first column of the lookup range for the matching value.  In our example, the lookup range was columns A and B of the “Products” sheet, so Excel looked in column A for the matching value.  There is no way to tell the formula to look anywhere other than the first column; so you either need to cut and paste the columns to get the right one first, or just change the reference so the lookup column is first.

To complete our list, we can just fill down the VLOOKUP formula in column G to the bottom of our orders list.  Now we can analyze our order data with actual Product Names instead of just Product IDs.

Summary
VLOOKUP is useful in many other situations…you can probably imagine a few other uses for it yourself.  It is very handy to use it as we did in this example though.  Even though we could have accomplished the same goal by having someone in IT modify the query or report, now you can be a little more self sufficient with your reporting needs.

In my next post, I’ll cover a few more features like filtering and date manipulation.  Ultimately, we’ll move on to Pivot Tables and External Queries which provide very powerful mechanisms for analyzing data and can compete with some features offered in expensive reporting software.

Written by TedWimmel · Filed Under Business Intelligence, Technology 

Comments

2 Responses to “The VLOOKUP Hookup”

  1. smcwhorter on January 20th, 2010 3:29 am

    http://thefuturevalueofbusiness.com/the-vlookup-hookup.htm need to learn lookup in Excel?

  2. Jodie Heflin on February 25th, 2010 11:58 pm

    So Ted, do you ever used the HLOOKUP?

Got something to say?