Today, I want to show you how you can use the XLOOKUP function in Microsoft Excel. And just like the name implies, with XLOOKUP, you can look up values. For example, let’s say I have a chocolate chip cookie. That’s my favorite type of cookie, and I want to know how much does this cookie cost? I have a table with all of the different cookie prices. I can look up the price for my chocolate chip cookie. I could also go back and change the cookie type. And there I immediately get the price back for sugar cookies. I know, I know these cookies are ridiculously expensive, but hey, they are delicious. We’re going to start with the most simple example that shows you the fundamentals of how XLOOKUP works, and then we’ll go and look at some of the more advanced capabilities. XLOOKUP is currently only available for Microsoft 365 subscribers. If you don’t have that, I’ve also included a video right up above that’ll show you how you can use VLOOKUP and HLOOKUP. All right, let’s check out the XLOOKUP function. Let’s start with just a basic example that will show you the fundamentals of XLOOKUP. If you want to follow along, I’ve included a link to this worksheet in the description down below. Over on the right-hand side, I have a chocolate chip cookie, and I want to know how much will this cookie set me back. Over on the left-hand side, I have a table with cookie prices and the cookie type. I want to look for chocolate chip cookie in this table. So here I want to look for the cookie name, and then I want to get this price back into this cell. To do that, we are going to use the, you guessed it, the XLOOKUP function. And by far the easiest way to enter a function is to use the insert function helper. Let’s go to the top left-hand corner and click on this icon. Within this prompt, let’s search for the function XLOOKUP and then click on go. Here I see XLOOKUP, click on this one. This opens up the function arguments and over on the left-hand side, you’ll see that some of them are bold and some of them are not bold. So, what’s the difference? Well, the ones in bold are required and the ones that aren’t bold are optional. In this basic example, we’ll just focus on the first three. You only need these to do a simple lookup. All right, let’s figure out the price for a chocolate chip cookie. Right up on top, it asks me for the lookup value, and once again, I want to know the price of a chocolate chip cookie. So, this is what we’re looking up. Next, it asks me for the lookup array. Well, I’m looking for chocolate chip in this table. And here I’ll select the cookie type column as the lookup array. Once it finds chocolate chip, I want to get the price back. So, the return array is the price. I’ll highlight this column. So here, once it finds chocolate chip, it’ll return this value from that same row. Down below, we can see that the price is five. And once again, I filled out all of the required fields. I’ll click on OK. And here again, we can see that the price is $5. Look at that. You successfully looked up your first value using XLOOKUP. Wasn’t that easy? As an added bonus, you also now know how obscenely priced our cookies are at the Kevin Cookie Company. Back within our Excel spreadsheet, here you can see the Formula for XLOOKUP. You could type it in directly like this, or you could use the function helper, just like we did. I personally like the function helper because I think it makes it a little bit easier. Within our formula, because I referenced the cookie type right here, I can now change the cookie name. So let’s say I type in sugar cookie, and I want to know the price for that. When I press enter, the price automatically updates to reflect the price for a sugar cookie. Now let’s say instead of searching for sugar cookie, maybe I’ll type in Kevin’s Ultimate Special Delicious Cookie. Now that sounds really good, but as you can see, that cookie type does not exist in this table. And I get this nasty #N/A error message back. To present a nicer error message, let’s once again open up the function helper. I’ll click on this icon, and here once again, we can see all of the function arguments. This brings us to our first optional parameter, and that’s if not found. So, if there’s a cookie type that just doesn’t exist, well, we can provide a friendlier message back to the user. Here I’ll insert a quote. I’ll type in no such thing, and then I’ll close my quote and click on OK. So, when we search for Kevin’s Ultimate Special Delicious Cookie, there is unfortunately no such thing. XLOOKUP works great when you have a vertical list of data, but what happens if you have a horizontal list? Here on the next worksheet titled Horizontal, XLOOKUP works just as well with horizontally presented data. Right here, again, I’ll click into the price field, I’ll select the function helper, and let’s click back into XLOOKUP. Here once again, I’ll look for chocolate chip. Here I want to look for it in this array of data, and once again, I want to get the price back. Here I’ll click on OK, and here too, we see that it’s $5. Along with horizontal lists of data, you can also use XLOOKUP across multiple worksheets. You’ve probably already memorized the price of our chocolate chip cookie, but once again, you can also use XLOOKUP across sheets. Here once again, I’ll open up the function helper, click into XLOOKUP, and here I want to look up chocolate chip. For the lookup array, well, there’s no table on this sheet. Here I’ll click into the price table worksheet, and here I’ll look for chocolate chip in this column. For the lookup array, here I’ll click back into the price table. I’ll select all of the prices, and then I’ll click on OK. So here too, I can get the price back, even if the table that I’m looking up the price in happens to be on a separate sheet. One of the nice things about XLOOKUP is not only can you get one value returned, you can have XLOOKUP return multiple values. Here on the return array sheet, not only do I want to look up chocolate chip and get the price back, but I also want to get the cost back, and once again XLOOKUP can do this. Let’s click on the function helper. I’ll click into XLOOKUP, and once again, we’re looking for chocolate chip. In the lookup array, just like we’ve been doing all along, here I’ll select the cookie types. For the return array now, not only do I want to get the price back, but I also want to get the cost back. So how do we do that? Well, here I’ll simply select both of these columns, and now I’ll click on OK. And look at that, here it gives me the price per cookie back of $5, and it spills over into this next cell, and it also gives me the cost back. When you get multiple values returned, you can nest that within another function. What does that mean? Well, let’s check it out. Before we jump to the next worksheet, here I’m going to copy the XLOOKUP formula that we entered, and I’ll copy it right to the point of the equal sign. Next, let’s jump to the combined functions worksheet. In combined functions, up here in the top right-hand corner, I want to know how much profit do we earn for every chocolate chip cookie sale. To calculate the profit, I’ll take the revenue, which is five, and then I’ll subtract the cost per cookie, which is two. So, the profit should work out to three. I could use the SUM function. I’ll sum the revenue and the cost, and that’ll tell me what the profit is. Right up here, let’s click on the function helper. Here I’ll type in SUM, click on go, and let’s click into the SUM function. Here within the SUM function arguments, it asks me what numbers do I want to sum up. Well, remember we copied the XLOOKUP function from the previous worksheet. Here I can simply paste that in here, and it’ll sum up five, and it’ll sum up the cost of negative two, and I can click on OK, and here I see that my profit is three. The neat thing here is you can nest your XLOOKUP within other functions, and in this case, I’m summing up all the different values returned by XLOOKUP. With XLOOKUP, you can also look up based on multiple criteria. Let’s take a look. On the next worksheet titled multiple lookups, I want to know how much does our classic chocolate chip cookie cost? And yes, that’s right, we now have a classic chocolate chip cookie in addition to a new chocolate chip cookie. What’s the difference? Well, I couldn’t tell you, but for whatever reason, we’re able to earn more on the new chocolate chip cookie. It might be like new Coke. To search for multiple criteria, let’s once again click into price. Let’s open up the function helper. Let’s click into XLOOKUP, and here for the lookup value, first off let’s select chocolate chip. Then I’m going to insert the ampersand symbol, and then I’ll select classic. So here we’re looking up two different values. In the lookup array, we do the exact same thing. Here I’ll select this column. I’ll insert the ampersand and then I’ll select this column. And then for the return array, I’ll select the price per cookie and then click OK. And here we can see that the classic chocolate chip cookie costs $5. On the next worksheet titled match mode, we’re going to explore some of the optional parameters. Up to now, we’ve been just doing an exact match. So we’ve been looking for a chocolate chip and we’ve been returning the price of five. And if we don’t find chocolate chip, well, we’ve returned an #N/A. But what if I have a customer come in the store and the customer says, I have $5.50 to spend on a cookie. What do you recommend? Well, I should recommend the chocolate chip because personally, I think that’s the best tasting cookie, but we want to maximize our revenue. So in this case, I’ll recommend the oatmeal raisin at $5.45. To recommend the oatmeal raisin cookie, I could use these different match modes. So here there is another exact match, but this time, if it doesn’t find an exact match, I could return the next smaller item. So here I could look for $5.50. It doesn’t find $5.50, so then it falls back to oatmeal raisin at $5.45. You could also run an exact match and if it doesn’t find one, it returns the next larger item, in which case it would be the white chocolate macadamia nut. And you could also do a wildcard search. So, let’s make this real and test it out. Here I’ll click into cell E2. I’ll click on the function helper. Once again, click into XLOOKUP. Here for the lookup value, I want to look up how much the customer has to spend on a cookie. And here I’ll look up in the price column. For the return array, here I’ll select the cookie type. And right here, there’s the option for match mode. And I want to recommend the next smaller item. So here I’ll enter a minus one. Here I can see that it’ll recommend oatmeal raisin. I’ll click on OK. And here we have our recommendation to the customer. In addition to match mode, you also have another optional parameter called search mode. And search mode is basically what order do you want to search in. And by default, you search from top to bottom. So let’s say I want to know when was Lola, one of our customers, when was her last order. By default, it’ll simply go from the top of the list to the bottom of the list. But here, if I go from the top of the list, this is the oldest order and it’s not necessarily her last order. Here I’ll click into the function helper. I’ve already filled out the standard XLOOKUP function. But here, if I scroll down, I see an option for search mode. And here I could perform a reverse search starting at the last item. Here I’ll enter minus one. I also have the option to use a binary search, but for that I have to either sort it in ascending or descending order. I’ll enter the negative one, click on OK, and here I see that Lola’s last order was on 7/13. So, yet another option that you can customize. We’re making fantastic progress on XLOOKUP. And last, I want to leave you with perhaps the most advanced and complicated scenario. You can use XLOOKUP to find values in a grid of data. To do this, we’re going to nest an XLOOKUP within an XLOOKUP. I know it sounds complex, but we’ll walk through it step by step. This now brings us to the very last worksheet titled nested XLOOKUP. And this is the final boss of XLOOKUP. You’ve made it this far, so let’s get through this. We’re going to use XLOOKUP to tell us how much money or revenue we earned from chocolate chip cookies in 2021. And here you can see that it’s a grid. We have the cookie type, and we also have years, and we want to pick out the revenue number from this grid of data. It looks really hard, but we’re going to break this down and we’re going to use two separate XLOOKUPs to pull this off. First, let’s return the revenue for the year selected. I’ll click into cell F7, click on the function helper, and here once again, let’s select XLOOKUP. For the lookup value, I’ll select the year. For the lookup array, here I’ll select all of the different years. And for the return array, here I’ll select all of the different revenue amounts and then I’ll click on OK. And here we see all of the revenue for 2021. Next, I want to find the revenue for chocolate chip. I want to look at this list here and when I find chocolate chip, I want to pick out this result. So I want to search against the output of this previous XLOOKUP function. Let’s copy this and we’re going to insert this into another XLOOKUP. Let’s click into cell C4. Let’s go back up to the function helper, and once again, we’re going to use an XLOOKUP. This time for the lookup value, I want to look for chocolate chip. For the lookup array, I’ll select all of the different cookie types. And for the return array, I could select this set right here and then click on OK. And here I see the revenue comes back as just over 8 million. But instead of pointing at this specific set, instead I can insert the XLOOKUP function directly into here. Here I’ll paste in the XLOOKUP function that we entered earlier. So now I have an XLOOKUP nested within an XLOOKUP. I’ll press enter and the result doesn’t change at all. It’s still exactly the same, except this time it’s a little bit more efficient and we could remove this XLOOKUP. Now that we have the formula in place, here I can change the year or I could change the cookie type, and here we see it automatically updates to the correct value. So here I can see it updates. So here now we are successfully searching across a grid. All right, well, you learned two very important things today. You now know how to use XLOOKUP. You also learned how to look up cookie prices. So, the next time you place an order at the KevinCookieCompany.com, you’ll know exactly how much it costs. To watch more tutorial videos like this one, check out the playlist right up above. Please consider subscribing, and I’ll see you next time.