02 Lookup Functions (VLOOKUP, HLOOKUP)
One of the reasons why Excel is a great piece of business software is that it is not simply about mathematical calculations – it also integrates features that are normally from word processors (formatting and publishing) and database tools (like Access querying).
If you’ve ever been forced to manually link cells between tables or go line-by-line to find a piece of data, you can probably benefit from one of Excel’s most commonly used “lookup” functions. We’re going to look at a couple of simple examples and we’ll wrap up with a real-world example using data from the World Bank.
There are three types of lookup functions in Excel:
- VLOOKUP
- HLOOKUP
- LOOKUP (we are not going to cover this because it does not apply to Excel 2003 or 2007)
VLOOKUP
It might seem scary at first, but I promise that VLOOKUP is actually very easy once you understand how to set it up.
We’re going to a very small set of data to illustrate the concept and then show you how it can apply to larger data sets in the real world. To start, here is a sample set of employee information that you might find in standard set of HR or financial data. In this case, there are only three data points, but remember that what we show you is scalable – it will work for much larger sets as well.
Employee Payroll Data:
| Name | Pay rate | Phone Number | Office Location |
| Joe Smith | $15 | 123-345-5678 | New York City, USA |
| Jane Doe | $25 | 987-654-3210 | Washington, DC USA |
| John Doe | $20 | 111-222-3333 | London, England UK |
Before we get ahead of ourselves, let’s look at the basic set up first:
=VLOOKUP( what to look for , where to look , column w/ desired data , match type )
Here’s a scenario: you have a separate document where you have to calculate holiday pay for specific individuals (problem: what is the employee’s pay rate). In this case, let’s say you need to calculate the amount for Jane Doe. To do this manually you could link back to this payroll data by searching through the payroll table row-by-row…. or…. you could use VLOOKUP instead!
Back to the set up:
=VLOOKUP( what to look for , where to look , column w/ desired data , match type )
What to look for -> in this case, its the name of the employee (Jane Doe)
Where to look -> you have a payroll table that connects an employee’s name with their pay rate
Column with desired data -> this refers to where the desired information is on the table. In our case, the pay rate is in the second column, so this would be 2. Note: this is the column number in the table that you reference, not the column in the whole sheet. You can check yourself by counting the columns starting from the leftmost column in the range.
match type -> gives Excel a condition for matching your “what to look for” with the “where to look” table. Here’s the way that I think about it:
- 0 (Zero) or FALSE means you only want an exact match. This is by far the most common, and my personal recommendation for almost all applications
- 1 or TRUE or Omitted will find the closest match. Note: this only works with numbers. Excel can’t find the closest match with text lookups.
Putting it together, we have a dynamic way to “query” the data table and get the information that we’re looking for. Here’s how it looks in Excel:
Now say you have to John Doe’s phone number. How do you think that you would do that? You can simply change the name in the “what to look for” line and change the column number to 3 (Phone numbers are in the third column).
Check out this example in Excel: VLOOKUP
HLOOKUP
We’re going to go through this quickly because HLOOKUP is very similar to VLOOKUP and its also much less common. The fact is that for ease of use, most data is presented vertically (headers on top, data falling onto rows). We’ll use the same employee data, but now orient it differently.
Employee Payroll Data:
| Name | Joe Smith | Jane Doe | John Doe |
| Pay rate | $15 | $25 | $20 |
| Phone Number | 123-345-5678 | 987-654-3210 | 111-222-3333 |
| Office Location | New York City, USA | Washington, DC USA | London, England UK |
Here, the headers are on the rows and the data falls into columns, moving out to the right.
Back in Excel, everything is pretty much the same, except that you’re now returning the row number instead of the column number.
There you have it! And if you want to see it in Excel look here: HLOOKUP
Before we finish this post, I promised a real example with real data.
VLOOKUP in the real world: Doing Business data from the World Bank
The World Bank publishes tons of data every year, and one of my personal favorites is the Doing Business report. I recommend checking out the WB’s data website if you’re interested in other global economic data.
If you want to follow along, download the raw data (Doing Business 2010) in Excel format here: Doing Business
I’m also going to throw in an added trick (Data Validation lists) that we’ll cover in another post. Plus the result has a VLOOKUP within a VLOOKUP! So download the solution here, and try playing around with it: VLOOKUP Example
As always, if you have any questions, get in touch.
