 What's going on, everybody? Welcome back to another video. In this Excel tutorial, we'll be looking at XLOOKUP. Now, if you don't already know what XLOOKUP is, it is a new feature in Excel to kind of replace VLOOKUP or to be a much better option, at least in my mind is a much better option than VLOOKUP. And so if you're someone who's either used VLOOKUP a lot and you're trying to learn this new option or if you've never used it before, this video will be super helpful because I'll walk you through kind of the options and what XLOOKUP can do as well as the difference between XLOOKUP and VLOOKUP. But before we get into the tutorial, I want to give a huge shout out to today's sponsor, and that is Udemy. Udemy is the go-to place. If you want a full fledged course in Excel, I have three options of courses that I have taken on Udemy. So I'd highly recommend checking those out. They are having a huge sale on all their courses during this time. And so if you are in the market for a course, I highly recommend checking out Udemy and getting one there. Now, without further ado, let's jump on my screen and start the tutorial. All right. So let's get me off the screen because we all know why we're here. So I didn't include this in the formulas video last week because I knew this was going to be a large one. And a lot of people are going to want to know how to do this with the difference between VLOOKUP and XLOOKUP is. So it has its own dedicated video to it. So let's get started. It is a formula. So we're going to come in here in the cell, we're going to hit equal, and then we're going to start typing XLOOKUP. Now, I'm going to hit Tab in just a second. But let's read what this says. It says, search is a range or an array for a match and returns the corresponding item from a second range or array by default. An exact match is used. So really useful to know. We'll talk a little bit more about that in just a second. Let's hit Tab and it's going to complete it. And it's going to start giving us or it's going to tell us what our input values need to be. We're going to have our lookup value. We're going to have our lookup array, our return array, and then some optional things like if not found. So if your option isn't found, what will be the output that it gives us? A match mode and a search mode. And I'm going to show you how to use every single one of these things. As you can see at the very bottom, I've already set up all of the instructional content for this video. And so we'll get through all these different scenarios. So let's just start really quickly with how to use it very simply with the lookup array and return array. So we're going to come in here and we're going to give it our lookup value. Now Toby Flenderson right over here in A3 is going to be our lookup value. So that's who we're going to be searching for. Now we're going to hit comma and now we're going to be needing to lookup or to input our lookup array. Now an array is just a, you know, our range basically. So we're going to do, this is where it's going to be searching for that value. This is where it searches for A3. So here's Toby Flenderson. Here's Toby Flenderson. So it will find it in this array right here. Then we're going to hit comma. And now we need to give it the return array, what it's going to return on that row when it finds it. So we're going to return his email, keep it really simple. So what it should do, and let's close parentheses, what it should do is it should take Toby Flenderson. It's going to search in this column or in this array. And then it's going to return the email when it finds Toby Flenderson. So it's on Toby Flenderson is on row six. So it's going to find Toby Flenderson. It's going to come over here and it's going to return Toby Flenderson at dundermilthlandcorporate.com. That's what it should do. Let's see what it actually does. It said enter and it returns it. Now if we drag it down like this, it'll apply it to all of these names right here. And it works exactly how it's supposed to. Again, if you have never used VLOOKUP, you don't know how good you have it. VLOOKUP was extremely useful but just a bit complicated. And I'll talk about that near the end of the video when we compare VLOOKUP to XLOOKUP. But just know that if you're using XLOOKUP for the first time and you're just getting into using Excel, you guys have it good. So just know that. Now let's go over here to XLOOKUP multiple rows because you can return more than one output with XLOOKUP. So let's go right in here and we're going to basically write the exact same thing as we did before. So let's write XLOOKUP. We're going to do Toby Flenderson as our value. We're going to search here and we're going to do something a little bit different this time. We want to include our end date and the email. So what we're going to do is we're going to start here. We're going to go down all the way to the bottom of end date and then we're also going to include the email. And when we do that, it will in the output give us a row or a column for end date and a column for email. So an output for both. So let's hit enter. And now we can see that we have the end date here and the email here. Now one of the downsides or something that I'm not a huge, huge fan of is, well, first off, I love that you can do this. That's fantastic. But it has to be right next to each other. So you're only going to get that output exactly how it is in the columns. So if I went and did this range, I would include all of that. So you know, let's just, for example, let's pull that down here. So let's take this and put it right here. If I did instead of zero or O2 to P10, if I included age to email this whole range and I hit enter, it's all going to be included. So, you know, that's one of the small downsides of that functionality of when you can use multiple rows is that it's going to use the rows exactly as they are. You can't really customize it within the formula. You can move around these columns to how you want it. So that is something to note. And again, you can pull this down and it'll be applied to all of those names. Let's go over to XLookup exact match. So let's open this up. We're going to do equals XLookup as we've been doing. And we're actually going to be looking at the if not found and the match mode, both, you know, on this tab right here. So let's do what we've been doing before. We take our value that we're looking up. We take the array that we're looking and we're going to do the email. And, you know, as you can see, this says Toby Flender, not Toby Flenderson. So what we are going to do is we're going to hit comma. And if it's not found, you can return a value or a string that you want to return. Now, for simple purposes or for simple instructional purposes, we're going to do not found. And then we're going to close that off. So let's do this. And Toby Flenderson was not found. And so it was returned not found. If Toby Flender was actually in this full name, then it would have returned the email. And then if along the way, you know, one of these was not part of it, then, you know, we would have, we would have had the not found. Alright, so let's go right up here. We're actually just going to copy this because I want to reuse it. And then we're going to go right here. I want to hit a comma. Now, this is our match mode option. And so we have four different options that we can choose from a zero is an exact match. And that is by default, that is what we have what we use. Then there's a minus one, that's an exact match or next smaller item. Then there's a one, which is an exact match or next larger item. And then there's a two, which is a wildcard character match. Now, we're going to do that. And we are going to, you know, try this out. And it's not going to work. And not just because I forgot to put a four. It's doing it because it's searching for Beasley. But if there's not a wildcard option already put in here, it doesn't recognize it. So we need to indicate where that wildcard needs to be. So we're going to do a double apostrophe or quotation marks. We're going to put an asterisk right here and then do another one. And we're going to hit an ampersand. So we're going to have an ampersand right here. And what that's going to say is anything that comes before a four, anything that comes before Beasley is okay. It doesn't matter what it is, as long as it has Beasley at the end, that is going to be okay. So we're going to have Pam that comes before Beasley. And that's going to tell it and it's going to say, okay, I know that anything that comes before Beasley is all right. And so when we hit enter is now going to return the output that we are looking for. And we can include that on these as well. Now, this one is Meredith. And so Meredith is at the beginning. So we have Meredith Palmer. So we can actually take this and we're going to put this at the end, the ampersand right here. And now it'll work. And the exact same thing for Kevin Malo right here, Kevin Malone. So it just didn't include the NE at the end. And so it's still going to work if we include that asterisk at the end. Now, I know I said we were looking at search order, but I'm actually going to kind of give you an exact match first and then search order. But it's just kind of easier to show it over here. So I'm going to do X look up. I'm going to look up this value and do a comma. Here's the range. This is our start date that's going to be looking for. And I want to return the full name. Now, no value in here has 112000. But what we can do is we can do comma and then a comma for the match mode and do an exact match or next larger. And I know this is an exact match part, but it kind of refers to search order in a little bit where it searches for the next largest value. That's what that number one represents, the next larger value. So we have 112000. And if we look right here, the next value above 112000 is 152000. And so it should return Angela Martin. Let's see if that works. And there it is. Now let's look up the actual search order. So let's do equals X look up. This is the value that we want to be searching for. And we're going to be looking in this start date and comma. And we want to return the name. Now let's get over to search mode. Now the search mode performs a search starting at the first item. So at the very top going down. So by default, it searches from first to last, but you can reverse that and do search from last to first. We're going to do a binary search, which is where it sorts in ascending order or sorts in descending order. And that's with the actual value. And so we won't be able to show this binary search or ascending or descending because our values are the same. But if we had different values and we were looking up using this next largest, we would be able to show that. But I'm going to show you the search from first to last and last to first. So let's put in by default. And this is what it would be search from first to last with the default would be. So it starts at the very top. It goes down and finds the first five, six 2001 and returns Toby Flandersen. Now if we go in here, and we hit minus one, that is going to search from last to first. So it's going to start at the bottom and go to the top and the first one that it finds is Michael Scott. So that's that first one starting from the bottom. And then the Michael Scott right there. So these two, the exact match in the search order can kind of be combined into this one right here. We're using this one, which is, you know, exact match or next larger. And you can include that in this binary search in this one as well. All right, now let's head over to the XLOOKUP horizontal. I think we're, we have a few left. Yep, XLOOKUP horizontal, then we'll do XLOOKUP with some. And then I'm going to show you the VLOOKUP at the end. So let's go right here. Let's say equals XLOOKUP. The value that we want to be searching for is February. That's what we're looking for hit comma. And where do we want to search to find February? We want to search in these calendar months. And then we hit another comma. And now we're going to be searching for paper. So let's do paper and we'll hit enter. And it found February and it returned paper right here. And we can do that for paper, printer, and manila folders. And so it's going to give us the 310, the 40 and the 118 from February. Now let's go right over here to XLOOKUP with some. I actually, it's basically a carbon copy of this. Let's take this over here real quick. And place it right there because it's the exact same thing except at the end we're going to use, I'm going to show you how to use some with the XLOOKUP at the same time. Now we're going to be using the formula SUM and so we're going to SUM. And then within the SUM our first number is going to be an XLOOKUP. And then our next value is also going to be an XLOOKUP. So let's do XLOOKUP. And now we're going to search for our very first value, oops, our very first lookup value. So we're going to go to I1. And then we're going to search this again. And we want whatever value, oops, goes into that. So let's close that parentheses. And now we're going to do a colon and another XLOOKUP. And now let's do March. So now we're going to search for March. We're going to do our search range where we're searching for that March. And we want the paper as well. And let's close that. And then we also need to close that parentheses. So now we are basically adding this February and this March. So it's going to be 310 plus 150. It's adding those two values. And it should be 460. So let's see if that is our output. And it is. So you can do this with a lot of things, not just SUM, but you're able to use XLOOKUP within different formulas. If you're searching for a specific value and a specific value in another cell, you can add those together using XLOOKUP, which is honestly, it's pretty great. So let's go over to VLOOKUP. So I wanted to show you this because I wanted to show you where it came from and what we used to do, unless you are continuing to use VLOOKUP and what we can do now. So XLOOKUP, I just showed you kind of everything. But super quickly, I'm going to show you how VLOOKUP used to work in a super short way so that you can understand how it used to be used and how it is used, how XLOOKUP is used now. So let's go in here and we're going to say equals and we're going to do a VLOOKUP. And so we have a lookup value. And so we're going to click this, we're going to hit comma, just like we did before. And now we're going to do a table array. And the table array is a little different in that you're searching an entire area. So let's do H2 all the way through O10. So that's what our table array is going to be. Then we're going to do a comma. And now we have to do a column index number. Which number are we going to be searching for? Which value are we going to be searching for in here? And so we want to search for 8 because this is 1, 2, 3, 4, 5, 6, 7, 8. We want to return that email and we're searching for the name right here in this very first column. So we have that comma and we're going to do 8. And then in the range lookup, you can do true, which is an approximate match or false, which is an exact match. And we'll do false. I don't know why it's not auto doing it. But there we go. And now we will do it. And it's going to return it just as we had it. A lot of people, I guess not everybody, but some people didn't like and the reason why they created X lookup, you had to do those ranges. And if you ever went in here and then we, let's say, we added another column, which happens to data, now it gives us completely different data. So let's say for whatever reason we added address. So now we have these people address. Well, now it's going to give us a different value. It's going to have this end date because if we go in here, now it doesn't, now the eighth is this end date and the ninth is this email. So if you have a V lookup that you use for a calculation or a table that you've created or different things in Excel, you then have to go through here and manually change this. And so a lot of people didn't like that because if you needed to change data or you needed to change something or add an additional column, you'd have to go back and fix all of your V lookups. They wouldn't just automatically move with it, which is what happens with X lookup. And just to prove this, let's go back to the very first one, which is the X lookup. And right now the email is looking at O2 and through O10. We're just going to insert right here. And that would be our new column. We'll do address. And notice that hasn't changed. And why is that? Because it auto changed for us from P2 to P10, understanding that it wanted to stick with when something was inserted here, wanted to stick with the original data or the original array that was selected. And so X lookup does that work for you. And it makes it a little bit easier to automate things and create these processes in Excel without having to go fix it later, which you had to do with V lookup. So that is it for today. I hope that you know how to use X lookup a little bit better now that you have watched this. If you enjoyed this video, be sure to like and subscribe below. I will see you in the next video.