A recent and very welcome addition to the local family of functions in Excel is X. Look up and look
up.
Here's a modern flexible replacement for older functions like Velar Kump H.
Look up and look up.
Much easier to understand and most definitely shorter to type out.
X lookup can find values in vertical or horizontal ranges, can perform exact or approximate matches
and supports wildcards for partial matches.
X lookup can also search data starting from the first value or the last value.
So does this spell the end of V look up?
Well, maybe.
But before you get too sad, let's take a look at how easy X lookup is to use.
So let's take a look at how simple complex lookups can be using the new X look up formula.
Now, in this spreadsheet, I have a list of employee names and I have my data structured in a slightly
different way to what I might normally structure it.
So I have my employee name in column D.
I then have the day that they started in column A, the team they're currently in in Columbia and then
their location in Column C is really just to highlight the difference between X..
Look up and look up.
If we were going to use lookup to do something like this and I wanted to use the name as the lookup
value, which I could well possibly do in this particular example, the name would always have to be
on the left of the information.
I want to look up.
So I would need to move Column D over to the left if I wanted to use the name as my lookup value.
Now with X look up, we don't need to worry about that because much like index match, it can search
in every direction in your table of data, which makes it a lot more flexible.
Now, another thing I want to highlight in here before we get into constructing our first X look up
is that you can see here I have the current team that the employee is in.
Now, what you'll notice is that I also have a couple of repeat names in column D, so just to make
those stand out and it's going to highlight this and let's just do some conditional formatting and highlight
those duplicate values.
So you can see here I have Bonnie and I have her again down here.
Now, when Bonnie first started at the company, she was in the development team.
But I can see that a couple of years later, Bonnie actually moved to the special projects team.
So currently she's in the special projects team.
The same thing here for Kathy Wong.
She started in the I.T. team and then a couple of years later, she moved across to training and development.
So this is an important point to know, because with X look up, we can make the distinction as to which
one of these were actually looking for.
So let's just clear that conditional formatting and let's construct X lookup.
So for that, I'm going to jump across to the list worksheet.
Now in here we have a small table that has the employee names in Column A, we have this salary information
in column B, we have a job rating for each employee.
So maybe this has been gained from some kind of review or assessment and the rest of our columns are
blank.
So I have a column for team, a column for current team.
I then have a column for bonus and new salary.
And then right over on the right hand side, we have a smaller table which shows me some job ratings
and a bonus scheme as well.
So I'm going to use all of this information to populate these blank columns and we're going to use X
look up to do it.
So let's start out with the team.
So I'm going to type in equals X lookup, open my bracket.
Now, the first argument here is the lookup value.
So you look at value is always going to be that piece of information that's common between the two tables
of data.
So I'm going to use the name as my lookup value, comma.
I then need to tell Excel where I'm looking this name up.
So I want to look the name up in this array.
Let's jump up to the formula bar, comma.
And then the final argument I need here is just to specify the return array.
So I'm looking up the team, so all I need to do is select the team array.
Now we do have some other arguments on the end here, but those are enclosed in square brackets, which
means that they're optional.
Now, I am going to use those in further examples, but for this first one, we're just going to leave
it there.
Close our bracket here, enter and there we go.
Now, I want to drag this formula down.
So something I forgot to do, which I probably should go back and do, is make these cell references
absolute.
So let's just click up here and do a quick EF four and do the same for these ones as well and hit enter.
So now I can drag that down and it's going to pull back the team.
Now let's take a look at Bonnie and Kathy.
So for Bonnie, it's telling me that her team is the development team.
And if I go by.
Back to my other worksheet, I can see here that it's picking up the first team that Bonnie was in,
so it's not accounting for the fact that she changed teams a couple of years later.
And you'll find the same is true for Kathy Wong is telling me that her team is it.
But I know that more recently she moved to the training and development team.
So in the next example, I'm going to show you how you can essentially extract or look up the most recent
team.
So let's go back to our table.
We now want to see the current team.
So we're going to do our X, look up again, open our bracket.
I'm going to use the name as the lookup value again, comma.
Where am I looking up that name?
Well, I'm looking it up.
In this range of cells, and let's remember to press for this time comma, and I'm now working up in
the Formula Bar, I now want the return tray.
So I'm still pulling back the team, which is this array just here, an effort to lock in.
Comma, now this is where we move into our optional arguments, so I could specify something, for example,
to Output's if it can't find a value.
Now, I'm not too concerned about that.
So I'm just going to press comma to move on to the next argument.
Now, here I can specify what kind of math I'm doing.
Do I want to do an exact match, an exact match on next smaller item, an exact match or next larger
item?
Or do I want to do a wildcard character match?
Now, for this example, I'm matching the name and I wanted to exactly match, so I'm going to press
zero.
Incidentally, if you didn't specify anything here, then exact match is the default.
Comma, now this final argument is search mode, now this is where it's going to pick up the most recent
team or the current team that they're in, because by default, I do a search from first to last.
So it's going to pick up whatever it finds first in the table.
But what if I do a minus one here where we do a search from last to first?
If I now close off my bracket and hit enter, let's copy this formula down.
And you can see already for Bonnie, the current team is now showing a special projects.
And for Kathy Wong, it's showing us training and development.
So because I'm essentially searching from the bottom up, that's why I'm getting the result that I'm
getting.
So don't forget about that search mode, a really useful little argument.
So now let's work out if they're going to get a bonus and if so, how much bonus are they going to get?
Now, the bonus is based on their job rating.
And you can see we have a little table over here that tells us if somebody gets a job rating of five,
they're going to get a 10 percent bonus job rating of four, eight percent, so on and so forth.
And I have a job rating column just here.
Now, one thing you'll notice is that for some of these, we don't have what I call exact values.
So we have a four point three here at one point to a three point two, so on and so forth.
So if I was to do an exact match, it's not going to match that in this little table.
So how are we going to get around that?
Well, let's construct our look up
now.
I look up value this time is going to be the job rating, comma.
Where am I looking up the job rating while I'm looking it up in this array just here, J4 four to eight
and I'm going to lock those cells, comma.
What's my return array.
Well I want it to return how much bonus they're going to get.
So my return array is k4 to create a four to lock comma.
Do I want it to return anything if it can't find the value.
Well no, I'm not particularly worried about that.
So I'm just going to press comma to move on to my next argument.
Now it's this argument here, the match mode where I can specify if I essentially want to round up all
round down.
So where I have the job rating of four point three, do I want them to get the bonus for people who
have a job rating of four or people who have a job rating of five?
Now I'm going to say exact match or next smaller item.
So that is going to be a minus one.
Close the bracket answer and let's copy this down and see what we get.
So this person has a job rating of five and they are getting a 10 percent bonus, which is correct.
Now, this person here, Cody Fletcher, has a job rating of four point three.
He is going to get a bonus of eight percent because it's rounded down to four in this table.
This person here has a job rating of one point two, and they're not going to get a bonus because we've
rounded down to one and they get zero percent.
And let's check this one just here, three point two again, it's been rounded down.
So they're going to get a five percent bonus.
So now we can do a very basic some calculation to get their new salary.
So we're going to say salary multiplied by bonus and then we're going to plus the salary again.
And I can now double click to copy that down, so explicative is a great alternative to things like
Vlok up an index and match, the arguments are a lot clearer, a lot easier to understand.
You don't have to do as much typing.
And you also have more arguments related to how you're matching and the search mode that you're using
to make the whole formula a lot more flexible.
That's it for now.
I will see you next time.