How to Use XLOOKUP in Microsoft Excel

Hi everyone, my name is Kevin. Today, I want to show you how you can use XLOOKUP in Microsoft Excel. XLOOKUP is a new function that Microsoft is recently releasing that helps you find things in a table or in rows or columns of data. XLOOKUP replaces the VLOOKUP and HLOOKUP functions if you've ever used those before. I'm going to show you step-by-step how you can use XLOOKUP. I'm going to make it really easy so anyone can understand how to take advantage of this. As full disclosure, before we jump into this, I work at Microsoft as a full-time employee. I'm required to say that by my HR department anytime I talk about Microsoft products. Now, XLOOKUP is a new function. Many of you have probably not heard of that before and Microsoft is slowly rolling it out. It's currently available to Office Insiders, but it'll soon become available to everyone. If you're interested in getting on Office Insiders, I'll show you quickly how you can join that and then we'll jump in and walk through how you use XLOOKUP. All right. Well, enough talk. Let's jump on the PC and let me show you how you take advantage of it. So here I am on my Windows 10 PC and I have the latest and greatest version of Microsoft Excel on my machine. I mentioned I would show you how to join Office Insiders if you're interested in getting the latest version. If you're on Office 365, you'll be able to do this. If you click on the file pivot up on top, that brings up this backstage experience on home by default. And what you're going to do is scroll down to account, click on that, and then over on the right-hand side, you'll have the option of joining the Office Insider program and then you'll get the latest releases of Office. Okay, so let's jump into XLOOKUP and how to use this. And so I want to show just a very basic example of how you could use XLOOKUP. So just to orient you to the data, let's pretend that I'm a college and I have all these students in my school. So they have a student ID. There's a name associated with the student. You'll see that I'm a student in this school. There's also a home state and there's a major for each of these students. And what I could do is with XLOOKUP, I mentioned earlier that it helps me find values. And so what I'm going to do is why don't we just do a quick example here. So I'm going to type in equals. That's how we always start a formula in Excel. And then I'm going to type in XLOOKUP. So that starts the function and what I want to do is it asks me a few different things and we'll step through this one by one. And so what I want to do is I want to look up, let's say I want to know what Jack James, I want to know what his home state is. And so what I could do is I'm going to type in his name. I'm going to put it in quotes since I'm looking up a name. And so what I'm going to do is I'm going to type in Jack James and then close it with quotes. And what I want to do is I want to look up, so what it asks me to do is it says, well, what is the lookup array? And so basically what that means is in this table, what column has the value that I'm looking for? So I'm going to insert a column and then I'm going to say, well, let's look at this column here. So I could either select all these values or I could simply select column B. So I just want to look in this column. Now I'm going to insert a comma and it says return array. Basically what that means is when it finds the value that I'm looking for, so when it finds Jack James, return me some other column. And what I said before is I want to know the home state for Jack. And so what I'm going to do is that's in column C. So I'm going to select column C. And what I'm going to do then is I'm going to close the parentheses and hit enter. And so what that does now is you'll see that it's looking at column B and it says, hey, let me look for Jack James. It finds Jack James and then it says, okay, well then give me, return me the value from this other column C. And what I could do is instead of saying column C, I could say, well, what's this, what's Jack's major. And what I'll do is I'll select a column D and then I'll hit enter. And what that'll do is it tells me that his major is English. Now, one of the nice things about XLOOKUP is that you could also get back values from the left of the value that you're looking for. So what I'm going to do is instead of a column D, I'm going to look for column A, and then we're going to hit enter. And what you'll see is now it returned me Jack James's student ID. Now with VLOOKUP, that was a function that XLOOKUP is replacing. You could only go from left to right. And so you wouldn't have been able to get the student ID back unless the student name appeared first. So this is a nice improvement of XLOOKUP over VLOOKUP. Now, one of the other improvements too is, so here I'm returning for this one individual, I'm getting one value back. Whether it's the student ID, the home state, or the major. But let's say I want to get multiple pieces of information back. So what I could do is let's say I want to find Jack James, but I want to know his home state and his major. So what I could do is I'm going to select column C and D, and then I'm going to hit enter. And here you'll see it returns both Hawaii and English. And so I could get two values back. This is another nice improvement over VLOOKUP, which was the previous function that would return values to you. So this is a really nice way to look at tables and to get data back. Now, one of the really nice things, and so you might say, well, hey, I could just look for Jack James and I could see that information right there. How is this valuable? Well, let me show a quick example of kind of the great value of formulas in Excel. And that's when you fill value. So here, let's say I have another sheet with all these names and someone asked me, hey, fill in all the student IDs. Well, what I could do is I'll say, hey, XLOOKUP, and it says, look up value. I'm looking for Sean Reagan. And then I'm going to hit comma and it says, well, where are you going to look for his name? Well, let me go back to the previous sheet and I'm going to look in column B and I want to return the student ID. So that's a return value. So I'm going to select that. And then I'm going to close the parentheses and hit enter. And so you'll see now for Sean Reagan, they got me his student ID, nothing special there. We did that in the previous example. The only difference is here I'm on a different sheet, but now what I could do is I could simply drag this down and you'll see for every person named on this worksheet, I just quickly got the student ID back. So it looked up each person's name and it gave me the ID back. So that's a very quick way to look for or find data and then incorporate it into say another sheet or another place where you need some value from another sheet. At work at Microsoft, this is something that I use all the time and it's been something that's been very valuable for me at work. Okay. So we just ran through a basic example of X lookup. What I also want to show is there was another function called H lookup. And what H lookup does is it's very similar to what was V lookup, but it allows you to look for data when it's oriented in a horizontal way. So in this previous example, all of this data was oriented in a vertical way where you have column headers and then the data going down. In this example, I have the column headers or what are now row headers as rows and all the data goes across. And so what I could do is I'm going to type in X lookup and why don't we just find, we're going to look for, let's say Bill Berry. And what I want to do is, so I'm going to look for him in this row and then perhaps I want his major back. So then I'm going to select this as the return row. So when it finds Bill Berry, it'll get me computer science back. I'm going to close the parentheses, hit enter in here. It tells me computer science. So X lookup can both work across data when you're looking vertically, as well as data when you're looking horizontally, it works just as well. So pretty nice there. That's what you previously could use H lookup for. X lookup now replaces that. So very nice. Now, what I'm going to do here is I want to show another part of the function. So when I click into the function here, you'll see that not only do you have the lookup value, the lookup array, the return array, this is stuff we've already been talking about, but you have these other things like if not found match mode. And there's also something called search mode. So let's jump in and see what some of these options do. So here, what I've done is let's say that I'm looking for, let's just make up a name, Liz, let's say Benin. And Liz Benin is obviously not a student at our school. And so I'm looking for that name. And then I'd look in column B. And if I find it, I return her major. And so I'm going to hit enter. And it just gives me an NA because she doesn't exist as a student. So I'm just going to hit a comma here. And you'll see the next part of the formula says, well, if not found, what do you want to do? And in this case, I'm going to insert quotes because I'm going to type text and I'll say, student does not exist. And I'll close the quotes and just hit enter. And so what this does is if it doesn't find a student by that name, it'll simply return this text instead of showing me that NA. So this is a little more user-friendly doing it this way. So I kind of like that and I'm going to stick with that. So here, student does not exist. Okay. So this is great. The next thing that we're going to do is I'm going to look at the next part of the formula. So this is getting a little more advanced and using a few more of the features. If you just want to do a basic XLOOKUP, you should be good ready with the previous instructions. So here on match mode, what I can do is let's say that someone's income is $45,876. I want to find out what is the effective tax rate for every extra dollar made beyond this. And so you could see that, well, it's not in this group. The income here exceeds $39,475. And so for every additional dollar, this person is going to be in this tax bracket or the 24% because it's more than the 22% rate, but it's less than $84,200. So here too, XLOOKUP can help us. So let's type in XLOOKUP. And what we're going to do is we're going to look for this income. So I'm going to select this. And what we're going to do is we're going to look for the income in column B. So I'm just going to select column B, and then I want to get the tax rate back. Okay. And if I just hit enter now, what you'll see happens is, well, there's no income of $45,876 in here. It only sits between these different items. And so what I'm going to do now is we'll just continue the formula. And here it says, if not found for now, we're not going to fill that in. So I'll just enter another comma. And here it says match mode. That's the next item in the formula. And you have a few options. By default, it's set to exact match, and there was no exact match. So it returned nothing back. Well, what I could say is, well, hey, search for this number. And if you don't find it, give me the next smaller item back, which would be 39,000, or give me the next larger item. And that's what I want, because if it doesn't find 45,000, well, I want the next one up. And so I'm going to select this one here. And then I'm just going to go ahead and hit enter. And so what that does now is it looks for 45,000. It doesn't find it. And the next one up is 84,200. And the tax rate associated with that is 24%. And let me just throw the percent sign in there. And so there are 24%. It returned me the tax rate. So another cool way to use XLOOKUP. Now, what I want to do is this is now getting very fancy of how you could use XLOOKUP, but you could do nested XLOOKUP. Now, this is kind of like in the movie Inception, where you could have a dream within a dream. But that's what we're going to do here with XLOOKUP. We're going to have an XLOOKUP within an XLOOKUP. And what I want to do is I want to know what is the profit for quarter two. Well, quarter two obviously is 174,804, but I could use XLOOKUP to get that. And I'll show you how this is really cool as we go through it. What I'm going to do is I'm going to type an XLOOKUP in here. And what we're going to do is we're looking for the profit. So I'm going to go ahead and select profit here. And what I want to do is I want to look for it in this set. Okay. And now for the return value, what I want to do is this is going to be another XLOOKUP function. So we're going to type in XLOOKUP again. And now what I want to look up is I want to look up the quarter. So I'm just going to select that as the quarter over here. And then where I want to look that up, well, I want to look that up across the top. So across this header here. And then in terms of what it's going to return, I'm going to select all these values. So I search for both this column and I, or I search for these rows and I search in these columns, and then I'm going to return some value within this table. And then I'm just going to go ahead and hit enter. And what this did is, so it's saying, Hey, for the profit in quarter two, it's 174,804. Now what's nice about this is what I can do is instead of profit, I could say, well, tell me the cost. And now it's tells me the cost, or I could say, you know, instead of the cost, tell me the revenue, there's the revenue. And I could even change the quarter and I could throw in quarter three. And you'll see that very quickly, depending on what I'm looking for, it'll very quickly return the result. So using XLOOKUP can allow me to find data very quickly, whether it's just looking at rows or columns or whether it's looking at a combination of rows and columns. Okay. Well, that's a very quick tutorial of how you could use XLOOKUP and some of the great functionality that's coming soon to everyone in Microsoft Excel to take advantage of this. You'll need a current version of Microsoft Excel. If you have Office 365 and you're an insider, you could start taking advantage of XLOOKUP today. If this video helped you learn how to use XLOOKUP, please give it a thumbs up. If you want to see more videos like this in the future, please hit that subscribe button. That way you'll get a notification anytime new content like this comes out. And lastly, if there are any other videos that you want to see me cover on this channel, please leave a comment down below. I read all of them and I'll add it to my list of videos to create in the future. All right. Well, that's all I have for you today. I'll see you next time. Bye.