Looking up information with VLOOKUP and HLOOKUP

- 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.