Structure data for use with lookup functions

- [Narrator] Among the many different Excel lookup functions. The most prominent one currently is VLOOKUP, vertical lookup. It's companion is called HLOOKUP. In fact it's the third most commonly used function in Excel based on Microsoft analysis of how people use Excel. But there is a new function on the horizon already in place available to Microsoft 365 users, eventually to all Excel users, it's called XLOOKUP. But before we talk about VLOOKUP and HLOOKUP and XLOOKUP, it's worth discussing a bit about how we lookup information. And it's not to say that we are always looking up information in tables, but many times when you're trying to lookup for example, a tax rate, or some kind of a score. You'll have a table, possibly oriented vertically the way this one is on the worksheet, in column I through J. We're off to the right, the same data exactly, but organized horizontally. When asked which kind of layout would you prefer, nearly all users would say the vertical one. It just seem to fit better on the Excel screen. Perhaps it's debatable whether it's easier to read. I think it is, but I think other people do too. Many times if you're trying to lookup at rate, you'll be looking at tables. Something like the ones we see here. And quite often the tables that we use are two columns or two rows. But that's not a limitation, and sometimes you'll have a multi-column multi-row lookup. Somewhat small in this case, somewhat larger in this case. And when it comes time to comparing two sets of data invariably lists like this, what they have in common is Social Security number. If you're trying to reconcile the data here or pull it together in different ways. The thing that's in common here is that Social Security number. But invariably, this kind of a list here will involve what's called a VLOOKUP, or its successor XLOOKUP. XLOOKUP by the way does encompass all the capabilities of VLOOKUP and HLOOKUP. And we will be talking about all these functions in later movies. In lists like this too, it's always best if you can have the data on the same worksheet, that's not always possible, particularly in the case of larger lists. And it makes formulas a lot easier to write if the table is nearby. If in column G here, in cell G2, if we're about to use a VLOOKUP or an HLOOKUP or an XLOOKUP, having those tables nearby as we see them here is going to make life a lot simpler and easier too. Structuring data can come at us in a variety of ways. And sometimes we work with existing data and existing tables, but it's worth noting how tables are structured. And when you are setting up tables too keep in mind that in lookup functions, sometimes we're looking for what's called an approximate match, but probably more common and more dominant is the idea that sometimes we're looking for data and exact match. If we're coming up with a tax rate here, we're not looking for 63,981 exactly in this list, it's not there. These are like break points. We would be using what's called an approximate type match. And that could be done with VLOOKUP here or XLOOKUP. If we're looking up in the horizontal table, we use either HLOOKUP or XLOOKUP. And if we're comparing larger lists like these to the right, we usually will be using VLOOKUP or XLOOKUP. And as I said earlier, whenever possible in these kinds of comparisons, it's best to have the two lists on the same worksheet.