- [Instructor] 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 in 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're going to type the status of that state
and it's going to return it right in cell I2.
Now I know I feel like I'm bringing out something
really complex, XLookup, after we've only done
a few sum formulas in AutoSum.
But the first point is is that XLookup is really powerful
and you'll be glad you know how to use it.
Two, it's definitely not as confusing as it may first seem.
I thought it was very confusing and a little intimidating
and I'm going to try and convince you that it's not.
And you'll see.
And third, it's also just a great way for me to show you
how to figure out how other functions work.
For example, once we start building this,
Excel makes it really easy for you to figure out
what kind of variables it's looking for.
I'm going to show you what I mean right now.
My cursor is in the cell.
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 an open parentheses
'cause 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 me to put in there.
So right now it wants the lookup value.
We're going to look up the contents of cell I1.
So I'm going to put that in
and I separate the parameters with a comma,
and as expected, it's now moved to the second parameter.
Now it wants the lookup array.
That is what column are we looking up that value from?
And I can just click right on the column header of column E
because we're looking up the state.
I'll put in another comma,
and again, the next parameter is bold-faced,
and now it wants to know what type of data to return.
Once it finds a state, I want it to return the status,
so I wanted to return that from column F.
And now it's asking us what to do if it can't find anything.
I can put in a nice, friendly error message.
I need to put quotes around it
because it's just a regular text string.
I'll type Not Found.
And now I need to know if it's going to search
for an exact match.
This is an optional parameter,
but in this case, I'll put in zero.
I want an exact match.
And now I need to know how to search this column.
The default is just fine.
It's going to search from top to bottom.
That is first to last.
I'll put in a comma.
and that's it.
We've hit the end.
So I can actually get rid of this comma.
And instead, put in my close parentheses.
I'll hit enter.
And let's see what happens.
It worked.
It looked up the status of Alabama.
And sure enough, that status is active.
Here's the great thing about using
these formulas and functions.
They can change on the fly.
Let's put something else in.
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 it wasn't found, so let's try it.
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 a 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 to the Formulas Ribbon tab
and I hold my mouse over a function,
here's these bold-faced perimeters.
It's telling me how to use it.
This is the exact same thing that's going to pop up
while I'm inputting it.