Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Using VLOOKUP to Search in a Column
6:30 with Tyler TallonNow that we understand the purpose of a LOOKUP function let’s look at how to actually set one up using the Excel spreadsheet we just looked at.
Downloads
Download the LOOKUPS spreadsheet to follow along. Remember, you'll learn best by doing this with me!
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Now that we understand the purpose
of a lookup function, let's look at
0:00
how to actually set one up using
the Excel spreadsheet we just looked at.
0:03
Before we create the VLOOKUP, we need to
make sure our data is formatted properly.
0:07
What I mean by that is that if
we're going to do a VLOOKUP,
0:12
we need to make sure our data is
organized in columns since a VLOOKUP
0:15
does a vertical search in a column.
0:19
So you can see here on our
spreadsheet that our data
0:22
is nicely formatted in columns
with the row at the top for
0:25
headers, county, state, and
then the number of bedrooms across.
0:28
Now we want to pull over the number
of housing units by county
0:33
to our rental price tab.
0:37
First, let's label column
H as Housing Units.
0:39
After we do that,
we can select the cell below the header.
0:46
The first cell we want to
see the new data in and
0:50
then go up to the Formulas tab,
Lookup and Reference.
0:53
And then at the very bottom,
you'll see VLOOKUP, select that.
0:57
Now you will notice a box
pops up with four blanks.
1:03
These are the four input areas for
the VLOOKUP formula.
1:06
The first is the Lookup_value,
which is the value you are looking for.
1:10
In this case, it would be the county which
is what we're using to match the data from
1:14
one tab to the next.
1:18
So for row two, we want to return
the number of housing units for
1:21
Anderson County so
we would select cell A2.
1:25
The second is the Table_array,
where the range you want to search in,
1:31
which in our example is on
the Population & Units tab.
1:35
When selecting the range, the Lookup_value
must be in the farthest left column.
1:41
So county or
column A will be our farthest left column.
1:46
The data you want to retrieve can then
appear in any column to the right.
1:50
We want to return housing units so
1:54
the range we would select
is columns A through C.
1:57
One thing I want to mention here, in
the example, the range I selected included
2:01
entire columns, meaning all the way
down to the bottom of the spreadsheet.
2:05
Now if we decide not to
choose entire columns for
2:10
the range, we need to make
the range an absolute reference.
2:12
On a PC, you can do this by placing
your cursor in the range selection in
2:16
the formula bar or
VLOOKUP box in hitting the F4 key.
2:20
For more on absolute references
including how to do this on a Mac,
2:28
please see the teachers' notes.
2:31
The absolute reference will
make sure the range is static.
2:34
So when you drag the formula down, it
won't change the placement of your range.
2:37
I usually just play it safe and
select the entire columns for the range.
2:41
The next parameter is the Col_index_num,
2:45
which is the number of the column that
you want to return from your range.
2:48
So in our example,
we are wanting to return Housing Units,
2:53
which is the third column
over in our range.
2:57
So we'll type in the number 3.
3:02
One important thing to remember here
is that VLOOKUPs only look right,
3:07
meaning you can't put in
a negative column index number for
3:10
it to search columns to the left.
3:14
The last input is the Range_lookup.
3:15
This one is optional.
3:18
You can either put false, which returns
an exact match, or if you put true or
3:20
leave blank,
it will return an approximate match.
3:25
It's best to use false when
working with unique identifiers.
3:29
In this case,
make sure the county names match.
3:33
And you'll want to use true in cases
where you're not matching on a unique ID.
3:36
But you're looking for the best match or
3:41
best category in cases where
there are no exact matches.
3:42
If you decide to use approximate match,
3:47
your data must be sorted in ascending
order, where you make incorrect results.
3:49
Once we've populated the four boxes,
hit OK, and you'll see the result.
3:55
Now we can see that Anderson County
has 20,116 housing units.
3:59
Let's check to make sure that is correct.
4:04
There's Anderson County and
20,116, so it worked.
4:11
So to recap, the VLOOKUP search for
Anderson County in the range we specified,
4:15
and it returned whatever was in the third
column on the same row is Anderson County
4:20
on our Population & Units tab.
4:24
Now we can simply drag this formula down,
we'll double click and
4:29
you'll see the formula copies
all the way down to the bottom.
4:32
Now let's say we also want to see what
the population of each county is.
4:36
We can also pull this over from
the other tab by using a VLOOKUP.
4:40
But this time instead of choosing
VLOOKUP from the formula tab,
4:44
let's try typing in the formula,
which is the way I usually do it.
4:47
The first thing we wanna do is
label column I as Population.
4:52
And then we can place our cursor
in cell I2 and type =vlookup( and
5:00
then you'll see a pop up that helps you
remember the four parts of the formula.
5:06
Remember for the lookup value,
we want to select county,
5:14
so you can do that by selecting it
with your mouse or you can type in A2.
5:18
Then put a comma, you'll put
commas in between each command and
5:22
now choose your table array which
is the range you want to search in.
5:26
So go over to Population & Units tab and
5:31
select all four columns this time since
we are wanting to get population.
5:33
Then another comma, and this time we
will type in 4 as the column index since
5:40
the population is the fourth
column in a range.
5:44
And then put another comma and
type in or choose false and
5:47
then close parenthesis and hit Enter.
5:52
Now, you will see the population for
Anderson County is 58.458.
5:56
Now, you can copy this
down by double-clicking.
6:00
Now that we've walked through a VLOOKUP,
why don't you try and
6:04
create an HLOOKUP by adding the population
to the tab labeled HLOOKUP- Median Rental
6:07
$ by pulling from the tab labeled
HLOOKUP- Population & Units.
6:14
Remember that HLOOKUP is
just like a VLOOKUP but
6:19
instead searches horizontal rows
instead of vertical columns.
6:21
After you've tried on your own,
6:25
you can meet me in the next video and
I'll show you how I did it.
6:26
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up