Picking up related data from list usually involves using the powerful VLOOKUP or HLOOKUP functions. Comparing large lists nearly always means using the VLOOKUP function. The layouts of the tables needed in lookup functions can be compared before deciding which of the two types is most appropriate.
- [Voiceover] Excel has powerful lookup functions, and if you go to the formulas tab in the ribbon, you'll see a category of functions, it's called LOOKUP and REFERENCE. The two most important functions for most people, if they are to use any of these functions, are the one we see at the very bottom called VLOOKUP, think of V for vertical. And above, we see HLOOKUP, think of H for Horizontal. We'll see these in subsequent movies. We'll talk about them in this movie. And also point out there are two other functions that we'll use from time to time for lookup purposes, the MATCH function and the INDEX function.
And one other function called CHOOSE is gonna be similar to some of the functions that we'll be using as well. We'll show you how to use these as well in later movies in this chapter. Now, in this particular worksheet here called Lookups, and we're looking at the file called O4-Lookup Functions. We're about to calculate a tax rate here in Column H, and there's a table off to the right. Now, possibly, we could be using an IF function here. Maybe we don't need to use any so-called LOOKUP functions.
But, by using an IF function here and trying to taking them out like this 70,000 looking it up on the table. That's gonna be somewhat unwieldy. We'll have to have nested divs, not a good choice. The two major functions that we mentioned for lookup purposes VLOOKUP and HLOOKUP, are likely to be used in this scenario. And, off to the right, we've got two different tables. This one is structured vertically. It tends to be easier to read than the one that's structured horizontally. But we could be using either one of them.
And there will be situations where you inherit a worksheet from someone else, and maybe there's a vertical table, maybe there's a horizontal table. You might not have the freedom to say, let's just start all over and create new layouts here. So sometimes, you'll be faced with the idea, you will be using HLOOKUP or VLOOKUP based on the formulas that are already in place. Now, sometimes, tables like this are located on a different worksheet. Sometimes, they're on the same worksheet. If you have any control over it, put these on the same worksheet where you're gonna be using the formula.
The function, for example, VLOOKUP. Now, if we were to look up a value here like 70,000, in a table like this, we will expect to get an answer, and you might not have expected it, but the answer will be 10%. The logic of both VLOOKUP and HLOOKUP will proceed along the lines of, here's a value, here's a set of values. Since we reach a certain value that's bigger than what we're comparing, we'll go back to here. So, ultimately, we will be using a VLOOKUP function here and get an answer of 10%.
If we were using the HLOOKUP function, we would be using the other table over here, we'd be getting the same answer. The mechanics of the two functions are very similar, we get similar kinds of results. I think it's pretty clear by the layout of these how much more common it's going to be that you'll see VLOOKUP functions. A vertical table is just easier to read. It fits on the Excel screen a lot better than a horizontal layout like the one we see here. There could be times when you've got functions already in place and maybe you're using these, you'll just have to continue using it.
Now, in this movie, we're not showing you how to use these, but keep in mind one other kind of scenario. It's not uncommon these days for people to try and reconcile large lists. So imagine one worksheet that has a lot of data and maybe down on the left-hand side, there's an ID number to identify the data for each row, and you've got another worksheet with similar kinds of data and, also, a left-hand column with an ID number or person's name. And trying to reconcile large lists will mean making comparisons with the two.
Those will nearly always involve using a VLOOKUP, a vertical lookup. So even though we're not showing you how to use those functions in this particular worksheet here, we're setting the stage for using VLOOKUPs, occasional HLOOKUPs. We'll also show you how to use MATCH, and INDEX, and CHOOSE in subsequent movies. But these tables, when you have the option of setting them up yourself, make sure they're on the same sheet. It's just gonna make life a lot simpler to use as we work with these functions. And, so, in the next movie, we will be covering, first, VLOOKUP, and then, later, HLOOKUP, MATCH, INDEX and CHOOSE.
- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets