(upbeat music)
- [Dennis] We're looking at a worksheet called XLOOKUP.
That's the name of a new function
introduced in late August of 2019
and available to Office 365 subscribers
with the latest updates.
This function is important,
it will replace both VLOOKUP and HLOOKUP
although those functions are destined
to be around for quite a while.
XLOOKUP is easier to work with,
it changes the default setting to match up
with the most common use of VLOOKUP
that's for an exact match.
And it eliminates some of the problems associated
with redesigning the layout of tables as you work
with Lookup functions.
Let's first talk about how we will use this function
for an exact match scenario.
In column A we've got ratings
for various people's names in column D
and we want to convert those ratings into a numerical score.
If we were using VLOOKUP
and referring to the table over in columns A and B,
we would first have to redesign the layout of that table.
VLOOKUP depends upon the idea of comparing a value,
let's say the word "fair"
with the leftmost column of a table somewhere.
And off to the left,
column B that's where we find the word fair
and other similar entries,
that's not the leftmost column
if we're trying to get answers out of column A.
But with the new function XLOOKUP,
we don't worry about that.
In fact, the term table array
that we would see if you're using VLOOKUP,
it doesn't even appear as we use this function.
Equal XLOOKUP, and by the way, as you type XL,
it's the only function there
so, you can simply type equal XL Tab.
The Lookup value is in cell E2, comma.
Notice we don't see table array
we see the term lookup array,
and then later return array.
Where are we looking up data here?
In this case it's in column B, comma.
And when we find a match,
we'll pull up data from column A
that's the return array, comma.
This is an exact match
and we can put in zero if we wish,
but that is the default.
So we don't have to put in anything else at all.
If it were an approximate match,
we'd probably be using minus one and some cases, one,
and there's an additional argument
for more obscure uses of it.
But for an exact match,
and that's the most common use for most Excel users,
when they're using VLOOKUP,
we don't need to do anything else here.
That's it, enter, there's our answer of 71.
We'll double click the lower right-hand corner,
copy this down the column.
And we see the other answers.
I'll simply press the function Key F2
we can see that a little bit more clearly.
If we were using a horizontal table,
things would be pretty much the same,
obviously these would be in different locations,
but just as with a VLOOKUP comparison,
where we needed to get information
out of the leftmost column,
if this were information in rows,
we wouldn't necessarily have to be getting data
out of the top row of a table somewhere.
So, when XLOOKUP is being for an exact match,
whether it's corresponding
with your potential use of VLOOKUP or HLOOKUP,
it just works more smoothly and easily.
And it is the default setting.
And that's it, that's all we need to do.
Now off to the right in this same worksheet,
we've got a scenario for an approximate match.
We're trying to look up tax rates based on these salaries.
And if we're using VLOOKUP here,
we would be laying out the data,
in the way that we see it here.
But now the difference will be,
is unlike VLOOKUP where approximate match
is the default setting, here it isn't.
So how do we approach an approximate match,
equal EXL Tab.
We're looking up data in cell J2, comma.
We're trying to match it up
with the data in column P, comma.
And we find the match, we'll go into column Q.
And if we want to make this function work
the way it works in VLOOKUP, we'll use minus one here.
If we do nothing else and press Enter,
we're choosing exact match
and we won't get an answer here.
But we want an approximate match.
And the way approximate match works with VLOOKUP,
we want to find either an exact match
or the next smaller item.
So 64,387 isn't found in column Q.
The next lower value would be the 55000.
We'll get our answers out of column Q
that's going to be 3%.
If somehow or other,
we wanted our answers to be the next highest value,
we'd use one.
We're going to use minus one, type it or double-click,
tab it into place.
Now in rare situations, but not here,
we can put in comma and consider possibly searching
from the bottom up last to first.
The default is first to last,
that's what we're using here,
we don't have to put that in.
In more rare circumstances,
possibly we'll use a binary search
to work with unsorted data.
So that's all we need to put in here.
We can press Enter
or possibly get rid of the comma first either way.
Enter, we have the correct answer.
Double click to copy this down the column.
And once again, I'll press the function key F2,
just to remind us how XLOOKUP is working.
So this is a valuable function.
Is going to replace ultimately VLOOKUP and HLOOKUP,
it's easier to work with.
And it's made that big change,
the exact search is now the default
makes it easier to use the function.
So this is a valuable addition to Excel's Lookup functions.
XLOOKUP.
(upbeat music)