Instructor: All right, so we've spent quite a bit
of time talking
about functions like VLOOKUP, HLOOKUP, index, and match,
which are Excel's real classic lookup
and reference functions.
But I've gotta share with you a sneak preview
of a brand new function that Microsoft just rolled
out in late 2019 called XLOOKUP.
Now the thing about XLOOKUP is that it's currently
only available for Office 365 subscribers.
So if you're using an older version of Excel
or a standalone version of Excel,
you currently won't have access to this function.
Now what I wanna do here is give you a quick introduction
to what XLOOKUP is and what makes it different
from functions like VLOOKUP or HLOOKUP.
And then in the next lesson,
we're gonna get our hands dirty and do a full demo in Excel.
So XLOOKUP is used to retrieve values
from a table or range by matching a specific lookup value,
just like a VLOOKUP, an HLOOKUP,
or an index and match combo.
The difference is that we've got brand new arguments
that make this function much, much more flexible
and dynamic than those classic lookup functions.
So taking a look at the syntax here,
you've got your three required arguments shown in blue
and then a number
of optional arguments as well shown in gray.
Now, the beginning
of this function should look pretty familiar.
You start with your lookup value,
what value are you trying to match,
followed by a lookup array,
where are you trying to find a match for that lookup value?
And here's where things start to get a little bit different.
The third argument
of an XLOOKUP function is the return array
and it's the location
of the values that you're trying to retrieve.
Now, if you recall with a classic VLOOKUP,
you would enter a column index number here
or a row index if you're using an HLOOKUP
and you'd have to search to the right
or below your lookup value in order for this to work.
In this case with XLOOKUP, not only can we retrieve values
from anywhere in that table or range,
but we can actually return values
that span multiple columns or rows
with a single formula.
And that's new behavior called dynamic array functionality.
And it's something that we're gonna dive into in depth
in a brand new section of the course.
Now for optional arguments,
we've got the if not found argument,
which is basically saying,
"What if my lookup value isn't found in the lookup array,
what do I want to do?"
And this is great because historically,
when you're writing a VLOOKUP and you need to do this sort
of error handling, you'd have to wrap your entire formula
in an if error statement.
Now with XLOOKUP, that's natively built in right here inside
of this argument.
Then you've got match mode
which allows you to specify exact match or approximate match
or text-based wild card matching.
I'm gonna show you an example of each
of those different match types in the next lesson.
And then finally,
you've got the search mode, which allows you
to search your table or range top to bottom
or bottom to top.
That means if you have multiple instances
of your lookup value, you can use XLOOKUP
to find either the first or the last instance.
Now what I've done here is put together a little cheat sheet
of XLOOKUP versus VLOOKUP with some
of the key features of each and what sets them apart.
So quick recap here.
Like I said,
XLOOKUP can actually retrieve an entire dynamic array
of results with a single formula,
whereas VLOOKUP can only return a single value.
Next, remember that XLOOKUP can look anywhere
in the array, left or right, horizontal, vertical,
whereas VLOOKUP can only look
to the right, HLOOKUP can only look down.
Another interesting note here,
XLOOKUP is gonna default to exact match, which is what 99%
of users are gonna use this for, whereas,
the old VLOOKUP function defaults to approximate match.
Like I said, we support that native text wild card matching,
we've got that built-in error handling.
We can also find approximate matches in unsorted lists
whereas VLOOKUP required sorted lists
for approximate matches and then again,
XLOOKUP can go top, down or bottom, up.
VLOOKUP only searches top down.
Now that we have a good grasp on what XLOOKUP is
and why it's so important, let's roll up our sleeves.
in the next lesson, jump into Excel,
and practice putting this to use.