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.