- [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)