From the course: Excel: Advanced Formulas and Functions

XLOOKUP

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

XLOOKUP

“

- [Instructor] This is exciting, XLOOKUP. This was designed to replace VLOOKUP and be a lot more robust. As we go through this video, I will mention some of the differences between XLOOKUP and VLOOKUP. Okay, we got these transactions and this is a slight twist on the example from the VLOOKUP video. So we've got these transactions, we want to retrieve the reps name, we've got the ID, all right, so let's go over to reps and badges. Ah, okay, we got the ID and then two columns over, we have the names. That's what we want. And this is the first to point out about the difference between VLOOKUP and XLOOKUP. XLOOKUP can look toward the left. Let's do it. I'm in the column where we want names. Equals XLOOKUP, all right, double-click that. Lookup, what? Lookup the ID. Comma, look for it, where? I'm going to go over to the table. Look for the ID here in that column. Comma, return, what? Return the name. And notice, I am telling XLOOKUP what I want. I don't have to copy a lot of columns that I don't need in order to then count 1, 2, 3, or whatever the column that I want to retrieve. I'm telling XLOOKUP explicitly what I want to have done. Comma, if not found. Wow, that is part of XLOOKUP. We don't have to wrap it in an if n/a like we do with VLOOKUP. So we know that there are some people who no longer work here. So let's put double quote, double quote so that those transactions will remain blank. Comma, match mode, we don't care about that for this situation. We don't care about the search mode either. So I'm going to delete that comma. Close parenthesis on the XLOOKUP. Boom. There are the names and a blank for somebody who no longer works here. Check that out. It's all filled in and we were able to look left. All right, let's go over here. Check this out. We want to retrieve each person's location. So Connie's badge number is 309 and over in columns I and K, we have this data. It was really about the location and the phone number at the front desk, but then we added on later the badge numbers. But look at this, I'm going to highlight in that column and then go to Data and then sort ascending. This is the way that this would have to be if I use VLOOKUP, I've got to sort that look up range. But wait a minute, I want to see the data sorted alphabetically by the location. Put this back, okay, because we can do that with XLOOKUP. So I'm going to put location, right. Getting ready for the good stuff. All right. Equals XLOOKUP, double-click, look up, what? Look up the badge number. Comma look up array. I want to look up here. That's where I want to find the badge numbers. Comma return array, return the location. And I'm going to highlight it this way, because the tip menu is in the way. So we can see up in the formula bar that I do have table four location. That's what I want, that column. Comma if not found, I'm not so worried about that. Comma match mode. That's the exciting part. We want exact match our next smaller item. So that's negative one. I'm not concerned about the search mode right now. So close parentheses and Enter. Now let's see if this makes sense. Connie, badge 309, would that be Northwest? Well, let's go ahead and look at it this way. Sort, okay. From zero to 350 that is Northwest. That's right. How about Gator with badge number 1,604? And it's showing Gator's location is Cosmos. Yes. From badge number 1,600 and above, yes, the location is Cosmos. And now let's put this back to the way that we want to see it. There we go. All right, now I'm going to show you something really slick. VLOOKUP cannot do this. I am going to do a bottom up search, because we have these trainees and they had to complete courses. And here they are, here are the course completions in columns F, G, and H. The completion dates are sorted ascending. And notice, Molly is in here three times for course 1, course 2, and then course 2 again. Maybe Molly failed course 2 that was completed on the 19th of May and then retook it and passed it on the 16th of June. We care about the first date and the last date. All right, let's do it. Equals XLOOKUP, double-click, look up, what? Look up David. Comma, look up David, where? Here. Comma return, return the completion date. Comma if not found, I'm not worried about that. Comma, I want an exact match for David, zero. Comma search mode. Search first to last. So I want to put a one right here, close parentheses and Enter. So what we know is that Jean-Pierre was not required to do the training. And we have the dates. Those are the numbers that Excel users to store dates in the background. And these are the start dates. I want to go back to the beginning, because now I want the end dates. Equals XLOOKUP, double-click, look up what? Look up Gator, all right. Comma look up array. Look for Gator right here. Comma the return array, the completion dates. Comma if not found. I'm going to put a zero. Comma match mode. I want an exact match, so that's going to be zero. Comma, now I want to search last to first, negative one. Close parentheses, now I want to put a minus here, okay. Now I'm going to go back into the start dates that we calculated and I'm going to go to the if not found portion, and that was over here, okay. And I'm going to put a zero. Now Enter. This shows us the number of days that everybody took from the time that they started their curriculum to the time that they finished. And is not tripped up by people who took either of the courses more than once. We want the first one, the last one, and that's what we got and that's what XLOOKUP up gave us. XLOOKUP, play with it, fall in love with it.

Contents