- [Narrator] You may have heard of a function
called XLOOKUP.
We're going to build an XLOOKUP formula right now.
XLOOKUP enables you to retrieve values
based on a LOOKUP value in the same row.
So for this example, we're going to look up a specific state
in column E and retrieve the status of that state
from column F.
So right here in cell I1, here's a state,
and we can change which state we're looking for
and it's going to return the status
right here in cell I2.
Now I know it may seem like I'm bringing out something
really complex after we've only done a few SUM formulas
using AutoSum, but the first point is that XLOOKUP
is really powerful and you'll be so glad
you know how to use it.
Two, it is not as confusing as it may first seem.
And three, it's just a great way for me to show you
how to figure out how other functions work.
For example, once we start building this,
you'll see that it's really easy for you to figure out
what kinds of parameters Excel is looking for
for that specific function.
I'll show you what I mean right now.
My cursor is in the cell and I'm going to type my equal sign
so that Excel knows we're creating a formula.
And now I'll just dive right in and type XLOOKUP.
I'll put in my open parentheses
because that's how we start these functions.
And this is what happens.
The help file pops up and the bold faced item is currently
what it's expecting for me to put in there.
In fact, if I hover my mouse over this,
it becomes a hyperlink.
I can click on this and it's going to take me
to the help file so I can further look up
exactly what it's looking for if I'm not sure.
In this case, it wants the LOOKUP value.
We're going to look up the contents of cell I1,
so I'm going to put that in there.
And we separate the parameters with a comma,
and as expected, now this one's boldfaced,
it's moved on to the second parameter.
Now it wants to LOOKUP array.
That is what column, what data are we looking up
that value from?
And I can just select all of this data
because we're looking up the states.
I'll put in another comma,
and the next parameter that's bold faced,
now it wants to know what type of data to return.
Now, once it finds the state, I want it to return
the status, so I want it to return something from column F.
Now, this next parameter has brackets around it,
that tells us that it's an optional parameter,
meaning I could be done right now,
but in this case I want us to return
some nice friendly text if it can't find any values.
Because it's just regular text,
I need to put quotes around it.
But let's type, Not found.
I'll put in one last optional parameter.
It's asking if it wants to search for an exact match.
It's even telling me what I can put as a parameter.
In this case, it wants a zero.
To be done, I'll put in my closed parentheses
and I'll hit Enter and let's see what happens.
It worked, it looked at the status of Alabama,
and sure enough that status is active.
Here's the great thing about using these
formulas and functions.
They change on the fly.
Let's put something else in here.
I'll put in Colorado.
And the second I hit the Enter key,
it's going to recalculate.
Colorado is in fact inactive.
Now remember, we put in that status message
if something wasn't found.
So let's test that out.
I'm going to put in Apples, which is not a state,
and should not be in this list.
Sure enough, it's popping up with Not found,
which is that nice friendly error message.
So that's how you can use XLOOKUP.
And it's also a great example of how
you can use these other functions.
For example, if I go over to the Formula's ribbon tab
and hold my mouse over a function,
here's those boldface parameters.
It's telling me how to use it, and this is the exact same
helper text that pops up while I'm actually
inputting that function.