Join Dennis Taylor for an in-depth discussion in this video Looking up information with VLOOKUP and HLOOKUP, part of Excel 2011 for the Mac: Mastering Formulas and Functions.
- View Offline
- Exercise Files
- There are any number of situations in Excel where you want to look up information, perhaps located in a table that's on the current worksheet, or maybe on a different worksheet in the same workbook, or maybe even in a different workbook. Among the many lookup functions, the two most prominent are called VLOOKUP and HLOOKUP. Think of V meaning vertical, H meaning horizontal. In this worksheet called Lookups, there's a column, Tax Rate. We've got compensation amounts just to the left here.
We need to look up a tax rate. Now, if you had not heard about lookup functions, you perhaps would start to use an IF function here, and in theory you could use an IF function. It would be quite extensive, if you were trying to look up information in the table like what we see here, it might involve using nested IFs, perhaps eight or ten nested IFs. It would become quite complex. Now, when you're setting up information in a table, and you know that elsewhere you will be using functions to gather data from that table, you could structure a table vertically, like the one we see here in columns M and N, or you could structure it horizontally, the way we see the data here in columns P through Y.
I think that most people when they see the two kinds of tables compared side by side, nearly always would favor this kind of a structure. It just fits on the Excel screen in a more compact way. Now, by seeing these two tables, in no way am I suggesting that, when you're looking up information in tables, that they always are restricted to either two columns or two rows. We have other situations where maybe the table's more extensive. Off to the right here, is another kind of lookup situation, and here we want to look up a tax rate, based on the salary and the number of dependents, but the table here has seven columns in it and lots of rows.
So, some tables can be larger, some can be smaller. When you are looking up information from a table, it's not uncommon to have that table located elsewhere. Now the examples we're gonna be seeing in subsequent movies, the tables will be on the same sheet. That's gonna make it easier for the most part. Possibly, you might set up one of the lookup functions here in column I and then later move this table off the sheet. Sometimes it depends upon who will be using this worksheet, and whether seeing those tables is helpful or not.
Now, the mechanics of VLOOKUP and HLOOKUP, as we'll see, are very similar, but again, if you're setting up the table from scratch, if there's no pre-existing table, you're probably more likely to set up a table that's vertically oriented. And in addition to the VLOOKUP and HLOOKUP functions, there are also related functions called MATCH and INDEX, and also a lesser function called CHOOSE, that we will see in later movies. And again, the whole idea of looking up information is very common in Excel.
Another example of a lookup could be, you have one worksheet with a list of names, another worksheet, perhaps gathered from another source, and you're trying to match up the names. You want to see if a name exists in another worksheet, and if so, possibly pick up data in adjacent columns. So there are lots of variations on how we can use these functions within Excel. VLOOKUP and HLOOKUP will be our major focus, and then we'll also show you how to use CHOOSE and MATCH and INDEX.
- Using absolute, relative, and mixed references
- Using the Formula Builder
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding the use of nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
- Analyzing data with the statistical functions
- Calculating payment
- Performing basic math
- Determining dates and times
- Editing text with functions
- Analyzing data with arrays
Skill Level Advanced
Excel for Mac 2011 Essential Trainingwith Curt Frye6h 32m Beginner
Excel 2013: Managing and Analyzing Datawith Dennis Taylor2h 2m Intermediate
1. Formula and Function Tools
2. Formula and Function Tips and Shortcuts
3. IF and Related Functions
4. Lookup and Reference Functions
5. Statistical Functions
6. Power Functions
7. Selected Financial Functions
8. Selected Math Functions
9. Date and Time Functions
10. Text Functions
11. Array Formulas and Functions
12. Reference Functions
13. Information Functions
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.