- [Instructor] In the previous movie you saw how to use the sum and average functions. On this worksheet called Functions, we're about to use the function that you might not need if you're just getting started with Excel, but eventually you are likely to use what is the third most frequently used function in Excel. It's called VLOOKUP. It has a companion called HLOOKUP, and as of late August of 2019, a new function being introduced called XLOOKUP actually will replace VLOOKUP and HLOOKUP. And the whole idea behind a function like this is to allow you to pull together related information. Now, sometimes when you're doing this you're working with different worksheets at the same time. In the simple example we're going to show you here, we've got a list in column G of some ID numbers, and we need to associate them with an employee name. And over in columns A through D we've got a huge list, thousands of rows potentially. And rather than sorting the data and jumping back and forth to try and find these IDs, let's actually use this XLOOKUP function, the new XLOOKUP function. And by the way, that function does, in addition to combining the capabilities of these two functions, it introduces some new efficiencies and also gets over some of the limitations of the other two functions. Equal XLOOKUP, I'm about to type this in cell H2. If you've never worked with functions beyond sum and average, you're a little bit surprised, perhaps, as you type in a function name, you put in a left parenthesis, you will be seeing some indications as to what is required. And optionally, within brackets, some additional features too. This doesn't tell you everything you need to know. But most of the time when you use XLOOKUP you'll be saying, I've got a value, for example, the one in G2. We can click this or type in G2. We then put in a comma. Now what we see highlighted lookup array. Where are we looking? We are looking in column C. Now simply with the left mouse button, click column C, comma. And what are we looking for? We see the pop-up return array. We're looking to find the related information over in column A. And that's all we need to do in this particular example. I'll press enter. You don't have to put in a right parenthesis when using a function by itself. I'll press enter, and we have an answer. Now we're not verifying that just yet, but we do want to copy this formula down the column, point to the lower right-hand corner, that fill-handle, hold down the left mouse button, and drag down here. Now possibly one of those ID's is visible on the screen over here, in fact the last one is. Here's David Diaz right here, 30573, we happen to see that information right here in row eight. And there he is with 30573. So that's working there, and also Juan Bishop here up in row four. So we see at least two examples where it's working, so we're assuming they're all working. Let me also, as I click on the cell up here in H2, we can see in the formula bar, I'll also double-click momentarily so you can see it in more detail. We're looking up G2, trying to find it in column C, when we find it we jump into column A and return that amount. We could not have done this VLOOKUP because it cannot look leftward. That's what we're doing here, so this overcomes that limitation. And over in column K something similar, this time we're looking ID, and almost the same idea, except this time we'll be looking rightward. The lookup function XLOOKUP, we're looking at cell J2, comma, once again we're looking in column C, comma. And when we find that match we want to go into column D to find the compensation amount. And enter. And here too, like before, we'll drag from that lower right-hand corner, click and drag with the left mouse button downward. And one of those numbers might exist over there. This is 111,283, I think we have that over, yes we do, over in row nine. That's for Adam Frost. And his ID is 26621, that matches up with what we see over here in column J. Now, many times, and this is well beyond the scope of this course, you're likely to encounter if you look up in a much more sophisticated situation, and here too XLOOKUP will fill the bill. Two large lists, one column N through R, another column U through Z. Many times when you're trying to reconcile information and lists or pull them together, turn them into one list, you'd be looking at VLOOKUP and now in XLOOKUP to pull together the information from these different lists. A powerful function in Excel, XLOOKUP.
- Describe the process for adding a command to the Quick Access toolbar.
- Cite the shortcuts for Undo and Redo.
- Name the keys commonly used to move, copy, and insert data.
- Explain how to create a chart.
- Summarize the process for freezing and unfreezing panes.
- Describe methods for sorting data.
- Explain how to create Pivot Tables.
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.