Find and retrieve data with VLOOKUP and XLOOKUP - Microsoft Excel Tutorial

- [Lecturer] You probably heard a VLOOKUP, and if you haven't used it, we're going to do that right now. It can be a bit tricky, and it's supposed to be a more robust version of VLOOKUP. So I'm going to show you both, and point out some of the differences. Here we're looking at, in columns B, C and D, we have all of these transactions, the rep ID, the date of the transaction, and the amounts. We can scroll down. There are a lot here. The trouble is who is B326? Well, we look over on the right, columns J and K, we see B326 is Fernando. So we want to bring the names over from the right data set to the data set that's on the left, and both of these data sets are in tables. Let's check that out. Click inside a table, and we see the table design tab up top. Click away, and then click this data set. There again, table design tab. All right, let's get the names. Let's call it Names. That's easy. Widen this a bit. All right. =VLOOKUP, double click, lookup value. Lookup L259, comma table array. Here is the table. I'm going to go up here, wait for that black arrow, and I have to highlight both columns. Now, if you ever work with a data set that has more columns in between what you want to lookup and what you want to retrieve, you have to copy all of those columns in between. Right now we only have two columns. All right, so comma column index. When the lookup value is found, what column do we want to pull the data from? We want to pull it from the second column, comma. We want an exact match, so we're going to type false, close parenthesis and Enter. Because this is in a table, the formula copied all the way down the column, we have all of the names in here. Great. But there's a problem, right? There are errors at L259, K104. That N/A error means the VLOOKUP went to go find something that it didn't find, and that means... Okay, notice we have active employees here. Anybody who was not found, no longer works here. So we have to go back, change the formula, and I'm going to wrap this in equal ifNA, open parenthesis. Because we know that N/A is not truly an error. We know what it means. We can have it do something else for us. Comma, and then in double quotes, T/Q, for terminated or quit. Close the double quote. Close the parenthesis for the ifNA, and then Enter. Now everything is filled in. Everybody has the right name associated with their ID, and anybody who was an error is showing as terminated or quit. So let's do this with XLOOKUP Now, one thing about VLOOKUP is what you're looking up has to be the left most, and then what you want to retrieve needs to be right of that lookup range. We couldn't use VLOOKUP if these columns were reversed. Okay, let's go to XLOOKUP. We noticed IDs are two columns to the right of what we want to bring back. That's fine with XLOOKUP, so let's do it. Name, Enter. All right, make this wider. Equals XLOOKUP, double click, lookup value, the ID, comma lookup array. Now, I can just highlight what I want to lookup Wait for that arrow over the column. There we go. Comma, return what? Now we tell XLOOKUP what we want to return, comma. If not found, we don't have to fool with the ifNA. "T/Q", and the match mode defaults to an exact match. We don't have to do anything at this point, but I do want to show you, comma. Now look, these are the different types of matches that we can do, an exact match. Exact match or next smaller item. Exact match or next larger item, and wildcards. I'm not going to get into all of those. I just want you to see this, because XLOOKUP is so much more robust than VLOOKUP. So I don't want to use any of those. It defaults to exact match. So I'm going to hit comma. And then we have different searches, search first to last, search last to first, binary searches, we don't need any of those, so I'm going to hit Enter. Look at that one formula. G027 has retrieved Johanna. All right. And one other thing to show you about XLOOKUP. Here's something really cool that XLOOKUP can do. And without this, in the olden days, this was torturous. Here we go. We have data for people who took courses. They're all complete. Everybody has taken course one our course two, and are complete. And they might have even taken them out of order, but one rule is the courses have to have been completed within 180 days. In cell B3, we are curious about HC. When did HC take their first course? And that means we're going to do a plain XLOOKUP, =XLOOKUP. Look up what? Look up HC. Comma, lookup array. I want to look for HC in this column. And just to be safe, I want to hit F4 to lock down that lookup range. Comma return array, return the date. And I don't need anything more than that. Hit Enter, okay? And I need to deal with the formatting. I'ma select here. Go to this paintbrush. There's the format. And in anticipation, I'm going to hit the paint brush again, and make the formatting there. So now, here's the great part. =XLOOKUP, double click it. Lookup value, look for HC comma the look up array, lookup HC here, comma return, return the date, comma. We're not worried about if not found. Comma, match mode. We do want an exact match. That's the default. Comma, now we want to search last to first. Start at that last row, 12th of December, and then go up. So we want negative one right there, and Enter. And then look, confirm. Now we have the dates for HC's first course completion and then the second course completion. Now we can calculate the number of days in between. Equals this date minus this date. Is HC within the 180 day rule? Enter, yes, 167 days. There is so much to XLOOKUP, and there are other courses on the library where you can dig deeper into XLOOKUP. So I encourage you to check those out, and explore this yourself, VLOOKUP and XLOOKUP