Let's talk about Excel's new and improved XLOOKUP function. You might know by now that XLOOKUP is Excel's new superhero lookup function because it can replace VLOOKUP,
INDEX MATCH, and even more. Today I'll take you through five examples that will help you take advantage of Excel's XLOOKUP function. First example we're going to look at is how to handle items not found, so basically handling errors in XLOOKUP. Then we're going to take a look at using wildcards in the lookup, so looking for partial
match instead of full match. We're then going to do
a horizontal lookup. We'll use XLOOKUP instead of HLOOKUP. Then a two-way lookup, so lookup on the rows and columns. And finally how to look for an approximate match with XLOOKUP. Now I know a common question will be about the availability of the XLOOKUP function. Since that's changing, check out the description of
the video to find out more, so to see if you have it or if
you're going to get it soon. Now let's get to our five examples. (upbeat music) Here we have a list of employee names and their yearly salary. Now what I want to do is to calculate the maximum bonus each employee can have. On a separate tab, B_Master, I have the information on employee ID, the bonus percentage,
and the employee name. So in this case I don't have
any specific bonus scheme. Each employee can have their
own specific bonus percentage. What I want to do in the first step is to bring over that bonus
percentage to this sheet. Now here I just happen to
have the names, not the IDs. I'll just do a lookup based on the names. I'm going to start off with XLOOKUP. So the moment you start typing it in, you see it in the dropdown, click tab to select it. The lookup_value, what are we looking for
for the employee here? Next argument is the lookup_array, where can I find this name? Where is the complete list? It's in the B_Master tab, it's right here. Control + Shift + Down
to select the whole list. Press F4 to fix it. Next argument is the return_array, what do I want to get back? I want to get back the bonus percentages so I'm going to select this. Press F4. And we're pretty much done here. Notice that the other
arguments here are optional. We don't need them right now. We're going to take a
look at them in a second. But the default match_mode for XLOOKUP is to look for an exact match. So we don't need to specify
anything else right now. We can just press Enter and we're done. Since we've done the
fixing correctly here, let's just send this down. These are the percentages but we're getting some #N/As here. How do we handle this? The reason I get the errors here is because these names don't exist on the B_Master side. Now here's what I can
do if I'm using XLOOKUP. I can use the fourth argument, if_not_found argument. So what do I want to get back if my lookup_value doesn't
exist in my lookup range? In this specific case I want to give them a zero percentage bonus. So I'll just type in zero, press Enter, and send this down. Now I'm still not done with the formula, what I want to do is take this percentage and multiply it with the yearly salary and send this down too. Now ultimately you'll be creating an Excel table out of this, so you just have to
write your formula once and it's going to copy it down. Just to keep things simple and to make it easier to follow, I'm just using direct cell references. So this is going to be our maximum bonus. First task is done. Now what do we do though if
the table that we receive for the bonuses was arranged this way? So we had to do a horizontal lookup instead of a vertical lookup. It's no problem for XLOOKUP. Let's just try it here. So we'll just have to do XLOOKUP. Lookup_value, same value. Where are we looking it up? In B_Master. Our employee names are right here, so Control + Shift + and to the right to select everything. Press F4 to fix it. Return_array is this one, so Control + Shift + to the left, and go one back up. And let's fix these as well. So remember default match is exact match. That's it, press Enter,
and we have our values. Now just to make sure
we have the same values, let's just complete the formula by multiplying this with this and let's send this down. Everything is the same. How do we get rid of the #N/As? Just like we did before, let's use the fourth argument of XLOOKUP, and put a zero there. And we have the same
numbers here and here. So that's how easy it is
to do a horizontal lookup. And in both cases our lookup range was after the range that
we want to get back, and XLOOKUP has no problem handling this. Now let's take a look at
how we can use wildcards for our lookup_value. So let's say we just remembered the last name of our
employee called Willard, and we want to find out
what his first name is. Let's use XLOOKUP. The lookup_value is Willard, but because we're going to use wildcards, we need to put the wildcard
as a part of our lookup_value. So if we don't have it in a cell, and in this case I
don't have it in a cell, I have to type it in the formula, but because the wildcard
character is considered text, you have to put it in quotation mark. And then you just have to
type in the asterisk sign which is a placeholder for
text of variable length. Add the quotation marks again, and then connect this with
an & to the cell reference. Now if you want to add a second wildcard because this word could be
in the middle of the name, you can follow this with this as well, but in this case it's the last name, nothing is going to come after this, so we're good if we leave it in this way. Lookup_array is right here. Now I'm not going to fix it because I'm not planning
to pull this down. The return_array or in
this case is also here because I want to find the full name. So I'm just going to close
bracket, press Enter, and it's not going to
work because I need to use one of these optional arguments. This is not the one I'm
looking formula, if_not_found. Let's take a look at match_mode,
that's the one I need. I'm not looking for an exact match. So even if you use the
wildcard character in here, you still need to go in
and adjust the match_mode to accept wildcard character. So don't forget this part. Press Enter and then we
get the full name back. So if I change this to Cooper for example, I get Andrew Cooper back. Now let's take a look at how
we can do a two-way lookup. So I've just added maximum
payment to our list here by adding the yearly salary
and the maximum bonus each employee could get. To make it simpler to select, let's just quickly add
a data validation list. So go to Data, Data Validation, List, and for source, let's just
add the employees here, and click on OK. So here I can select an employee. Right here let's add a
second data validation where we can select one of these
fields here and go with OK. Now right here I want to see the number that's associated with James
Willard, maximum bonus. I need to do a two-way lookup. No problem. Let's use XLOOKUP to do this. First off, what's my lookup_value? I'm looking for James Willard. Next argument, what's the
range we're looking this up? Right here. Return_array. Well, now our return_array
could be any of these, right? Because it really depends
on what we've selected here. We need to bring this one in. I need to do a second XLOOKUP here because I need to figure
out which of these arrays, which of these ranges I
need to take for my lookup. So here I'm going to do another XLOOKUP. This time my lookup_value
is the maximum bonus. Where am I looking this up? In here. What's the array I want back? This array. In all these cases we're
looking for an exact match, so we don't have to worry
about the other arguments. And that's it. James Willard gets a
maximum bonus of zero. That looks good. Let's change this to
yearly salary, 39,627. Let's just copy the formatting here. Now let's change to Paul Garza. Yearly salary, 34,000, and maximum payment that we
have to pay him is 38,289. Now here's the thing if I highlight this and I press F9, notice that we get an array back. So our second lookup here is returning the entire list to our original lookup formula. And that's another thing with XLOOKUP is that you can do two-way lookups. Now let's assume the company just received a new bonus scheme. The new bonus scheme is right here and it's based on salary. So anyone who earns up to
30,000, they get 0% bonus. Between 30,000 to 50,000, they get 5%. Above 100,000, they get 15% bonus. That's the new scheme that
we want to apply to here. So let's calculate the new maximum bonus each employee can have. So we're going to start off with XLOOKUP. This time our lookup_value
is not the employee name because we're looking at the numbers. So we need this salary amount. The lookup_array is where we have our list of numbers which is right here. I'm going to fix it because I'm planning to copy the formula down. The return_array, what
do I want to get back? I want to get back these percentages. I'm going to fix it as well. Now I'm not done with the formula because this is going to
default to an exact match. I want to have an
approximate match instead. I don't want to put anything
specific for these arguments. I'm just going to skip it. For match_mode now, I'm
going to go with minus one, exact match or next smaller item. Close bracket, press Enter, and that's 10% for Gary Miller. So let's just take a look. Gary Miller earns a
little bit over 60,000. 60,000 is right here. So he earns 10% because he
falls between these two values, and he's taking the next
smallest item which is the 10%. And anyone earning a salary over 100,000 should have a 15% bonus. So let's just send this down, see who's getting 15%. Over 100, over 100, and also over 100,000. Now based on this we have our percentages, let's just calculate the maximum new bonus each employee can have. We're going to take this, multiply it with this salary, and make sure we change it to a number. So let's just copy the
formatting to this one and send this down. Okay, so this is how you can do an approximate match using XLOOKUP. You have the ability in match_mode to search for the exact match or the next smaller item or exact match or the next larger item. I hope you enjoyed these
five different scenarios of using Excel XLOOKUP. If you like the XLOOKUP function, give this video a thumbs up. Let me know below if you think you'll be using XLOOKUP
for your future files. That's it for today. If you're new to this channel and you'd like to improve your skills, consider subscribing. (upbeat music)
