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. Alright, 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 up 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 a 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 $5. 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 can 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 quotes 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 forward 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 a 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 we'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 & 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 & 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 can return the next smaller item. So here I can 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 can 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 lookup 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 all 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 can 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 sorted in ascending
or descending order. I'll enter the negative one and 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 a 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.
