Join Dennis Taylor for an in-depth discussion in this video Looking up information with VLOOKUP and HLOOKUP, part of Excel 2010: Advanced Formulas and Functions.
Some of Excel's most powerful functions are known under the heading LOOKUP functions. They're widely used for looking up information from tables, and these tables might be in different worksheets-- maybe even in different workbooks. In the setup in column G, on this worksheet called Lookups, we're about to figure out a tax rate here based on information that we might see in a table that looks like this, which is oriented vertically, or in a table that looks like this, which is oriented horizontally.
I think you can as you look at the data back and forth here, these tables, in a certain sense, are identical, and they are in terms of content, but certainly not in terms of layout. In column G, if we'd like to figure out the tax rate of the salaries that we see in column F, possibly we could use an IF function. If you have used an IF function, I think you know almost immediately how unwieldy this would become. The function would be horrendously long. You'd have to have about 12 nested IFs to come up with all these different answers here.
The VLOOKUP function, the HLOOKUP function, which we're about to cover in detail, both alternately perform the same task. If you look at the salary in F2, which is 41,639, you can see that that will fall into place here between numbers. The bigger issue though at first though is, when you're setting up a table, should you create vertically structured table or a horizontally structured table? In all my experience with Excel, and I would imagine in most people's instinct, if they happen to look at these two together, I think immediately most people would choose to lay out the data the way we see it here in columns A and K-- in other words, vertically.
So the appropriate choice to be using in column G here will be a VLOOKUP function. However, in a worksheet that already exists and maybe has some formulas built into it already--maybe you inherited this worksheet--there might already be some functions that are using this table here, either to look up data or for a reference point, and maybe you'll have to use the existing table. But when you're laying out the data, I think this is the much more efficient way. Now, we're going to see in some upcoming examples how most of the time tables are two columns, but I wouldn't say it is any sort of a rule, because in the same worksheet, off to the right, even know we won't be using this data, here's a table over here that involves calculating a tax rate by looking up information in a table that includes quite a few columns.
It could even have more. So don't in any way to limit yourself to the idea of the tables that are used for look up purposes or only two columns or two rows. But the initial idea here is when you are setting up tables, you think about which layout is likely to be best. And I think most of the time it's likely to be vertical. And that means that a VLOOKUP is generally preferable to an HLOOKUP, but sometimes you have to make the adjustment and accommodate existing data. So there are other possibilities here as well for structuring this data.
There are two other aspects to LOOKUP functions that we want to mention before we actually try these, and that's the idea that sometimes a LOOKUP is based on finding data in what we call an approximate way. We see the 41,639. Are we seeing that number here? Well, of course not. It's highly unlikely, although possible, that these salaries are going to match up perfectly with the numbers. We're trying to find numbers that fall between others, not trying to find them exactly.
On the other hand, there could be situations where you want to look up data. Maybe it's someone's name. You need to find it in a list. Or someone's Social Security number, you're trying to look it up in a list. In those cases, close enough isn't good enough; it's got to be exact. So, there are other kinds of LOOKUP functions too, and they could be either VLOOKUPs or HLOOKUPs, but sometimes you need to take into account whether the information has to be found exactly or approximately.
Many times when we're talking about an exact match, we're talking about text or ID numbers. Excel has a number of LOOKUP functions that allow us to find data based on tables structured either vertically or horizontally.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Reconfiguring cell data using text functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data
- Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
- Working with rounding functions
- Returning cell references