<div>0:00 Hey, so you simply want to find a value in a
range
. Let's say we have an A column here with a bunch of numbers all the way down.<br>0:06 Thousand different numbers. We have in the C column a bunch of different names, and we're like, ah, we just want to know if like there's one
number
we think of is it in this list?<br>0:16 And let's like pick five, four, six, six. So if we want to take this
number
and we're like, do we really want to scroll through this or do we want to copy it and command F, find it in the sheet, go down to it.<br>0:31 This is a bit complicated, right?
We want to do this in a
formula
. So I'm going to show you two formulas that you can do this with one, which is going to be
VLOOKUP
.<br>0:41
Search key
is going to be the thing you're looking for. And then the
range
is going to be here and
index
, I will show you why this is important later, but right now we're going to do one and then I'm going to do comma.<br>0:51 And we're going to do false. I do false all the time
is sorted
. Even if it
is sorted
and I know it's sorted, this is just
sort
of a fail safe for me.<br>1:00 I always put false. It's also optional. And there it is.
It's, it's right there, but like,
where
is it in here?<br>1:07 And what does it matter? Well, we can use
match
to say, okay, let's search for that key as well. That five, four, six, six, one, five.<br>1:15 Let's do the exact same
range
. We're going to
select
a colon a here, the a column. Our search type is going to be zero and it says one five four.<br>1:25 What does that mean? Let's scroll down and see if it's at online one five four and it is. And here is the name Marcus Pennington.<br>1:34 So why I chose the lookup here is because often we're looking for a value.
And we want other information from that.<br>1:44 So instead of doing
VLOOKUP
a colon a, I'm actually going to do a colon C. I'm going to include the names here.<br>1:54 And now if I just hit enter, nothing changes. But if I change this
index
from one to three, which means the third column from the left side, counting from left to right, I hit enter.<br>2:07 And now I get Marcus Pennington. So that's pretty cool. If you're looking for information based on the value in that
range
.<br>2:14 And if we want to use basically
VLOOKUP
presupposes that on.
The left side is
where
you're going to have your key information, your primary key, which is
unique
numbers or
unique
names.<br>2:29 It's always going to be on the left, but let's say we're looking for let's say we want to do this in reverse.<br>2:34 We're like, actually we want to find Marcus Pennington and we want to get the
number
that's in the A column.<br>2:40 And for some reason we can. Not move the A column.
We can't move it over to the right of the C column so that the C column of the names is on the left side.<br>2:48
VLOOKUP
presupposes that the left side is the thing you're searching for and then you're going to get information to the right.<br>2:54 So we can use this
match
over here and we're just going to
wrap
it in an
index
. So
match
over here gets us the row.<br>3:04 We know which row it is. Now we just need to get the row in the C column. So we just say equals
index
.<br>3:12
Reference
is going to be we have could be A or yeah A. Our row is going.
To be this F2 here and our column is going to be one.<br>3:26 And there it is. There's the
number
. We can also change A to A to C to C to get the C column and we get the name.<br>3:36 So this
match
can be wrapped with
index
. So let me show you that all at once. Let's say we have our name.<br>3:43 We're looking for Marcus Pennington here. We have in the D1 here. We have Marcus Pennington equals
index
and we're going to
reference
where
are we going to end up?<br>3:53 Well, we want to end up in a column.
We're going to do a comma and now we're going to write
match
in for row.<br>4:00 This
search key
is going to be Marcus Pennington. The
range
is going to be the C column. That's
where
we're going to look for Marcus.<br>4:08 We're going to find the
row number
and then we're going to get the
row number
in the A column. Now insert type zero and we can put a column here for one if we want and there we go.<br>4:18 We have now a little bit of search thing here.
Where
we can search for any name and get the
number
over here on the left side.<br>4:26 So this is really really helpful.
And if you're looking for something a little less dynamic, meaning this D2 is referencing Marcus Pennington.<br>4:35 If you're like, I'm always going to look for this text no matter what and I don't want it in another cell.<br>4:41 I just want the. Result instead of D2 put in quotes in double quotes the name Marcus Pennington. And there you go.<br>4:49 The exact same result here
where
we're looking up Marcus Pennington. We're looking up in the C column. Tell me which row is it in.<br>4:57 We're saying give me exactly the row. And then that
row number
is going to be the.
Row number
of the
index
of the A column.<br>5:05 So
index match
is a really good. Used to find a value looking up a value in this a column from a C column because it's the reverse of V look up and not the reverse.<br>5:16 It's a much more flexible version of V look up. V look up absolutely can do this, but you must have the thing you're looking through on the first column and then to the right, you need to have all the
values
that you want to get out of.<br>5:31 So V look up is a little less flexible.
And there's also X look up and H look up, but I really like
index match
.<br>5:39 I find it extremely flexible, versatile. To find that value and arrange and get all the other information you want from it across a whole sheet.<br>5:49 You can also re retrieve much more information here if you want. Bye.</div>