XLOOKUP is the planned successor
to Excel's VLOOKUP and HLOOKUP functions.
And at the time I'm recording this,
XLOOKUP is still in beta and only available to a portion
of Office 365 users in the Office Insiders program.
One thing that makes XLOOKUP so great is that,
unlike in VLOOKUP, you can search to the left,
and, in fact, the columns or row you're searching through
don't even need to be adjacent to one another
like they do in VLOOKUP and HLOOKUP.
Let's say we want to look up the associate ID for Henry.
So we'll start by typing =XLOOKUP, and the first field
we need to input is the associate name because
that's how we're going to find the associate ID.
And I have that right here in cell G2.
Next, we need to input the Lookup Array.
This is the column or row where
what we're looking up is located.
So in this case, it's going to be
these cells here for the associate name.
The third thing we need to input is the Return Array,
or where the value we want returned should be located.
So we'll select the associate IDs here to the left,
and, FYI, it's important that both your lookup
and result arrays are the same number of cells.
If not, you'll get an error.
So before we go on, XLOOKUP defaults to exact match,
which is what we want for this example.
So we can just press Enter and be done.
Henry's associate ID is 1396.
So these three inputs are the only ones required,
and, really, I expect that they'll be sufficient
for the vast majority of uses.
But there are still more options we can use.
Let's take a look.
So let's step back into our function and we'll add
a comma here at the end to move on to the fourth input.
Now this fourth input, if_not_found,
works like the IFERROR function, in that it lets us
customize a message to display should a result
not be found for our lookup value.
Let's say we input a message and we'll put this in quotation
marks because I'm gonna use text and say "Not in list."
And we search for associate Jill.
Now there is no Jill in the associates,
so rather than an error, what we'll see
is the custom message "Not in list."
So even though XLOOKUP defaults to returning an exact match,
you have options for close matches.
And you'll find these in the fifth input, the Match Mode.
So, for example, let's say we want
to look up the ID for associate Sasha.
But we're not sure if she spells
her name with a ch or an sh.
We can use a wildcard character to replace
the letter that we're unsure of, use a 2 in the match mode
to search for a wildcard character, and sure enough,
it returns associate ID number 1363 for Sasha.
If you're looking up a numerical value,
you can also lookup the next smallest closest match
or the next larger closest match.
Here's how.
So let's say we want to look up
the associate name for the associate ID.
And let's look up ID number 1372.
We'll type =XLOOKUP, the lookup value is the ID number.
The lookup array, this time we'll be looking for the ID
in the ID column, and we want to return the associate name.
We're going to skip the "if_not_found" error, and for match mode,
let's say we want to get the result for an exact match
if it finds one or the next smaller item.
So we'll use -1 and press Enter.
And there is no associate number 1372, but the smallest
closest match is associate Buddy, which is ID number 1370.
Now let's say we change this to a 1 and return
an exact match or the next larger item.
Press Enter, and this time, it looks higher than 1372
and the next value is Jamie's associate ID, number 1374.
You can use the sixth input, the Search Mode, to define
what order you'd like to search for the result in.
For example, our list is sorted by associate name,
then by their sale amounts for September, and then October.
So selecting a 1 for first to last will search
top to bottom, returning the September result, while
searching last to first will return the October result.
One important thing to keep in mind
when using XLOOKUP is compatibility.
So right now, the plan appears to be
to roll out to Office 365 users,
but not sure if other versions will also be updated.
So this means that if you use this function and share
the spreadsheet with someone who's using a different version
of Excel, they'll receive an error message
in place of the function results.
For more tutorials like this, click on these links.
Thanks!