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
In this video, we'll introduce you to lookup functions to find specific data points within a table.
Resources
Two different types of lookups:
- VLOOKUP - the V in VLOOKUP stands for "vertical"; it will search and find in a column, up and down.
- HLOOKUP - the H in HLOOKUP stands for "horizontal", it will search and find in a row, left and right.
When data is organized in columns, you'll typically use a VLOOKUP to search up and down.
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
You're back.
0:00
In this video, we'll introduce you to look
up functions to find specific data points
0:01
within a table.
0:06
First things first, make sure you have
a copy of the Google Sheets workbook found
0:08
in the Teacher's Notes.
0:12
Open it in a browser on your computer
to follow along with me if you wish.
0:14
Notice the tabs along the bottom,
0:18
these are the individual worksheets
we will use throughout the workshop.
0:19
I'll let you know which ones
you'll need for each video.
0:24
For this video, we'll use the first
worksheet titled Texas Rental Data.
0:27
It contains rental data and the population
for every county in the state of Texas.
0:32
Did you know there were
254 counties in Texas?
0:37
Anyhow, let's imagine that you're
planning to move to Texas and
0:41
you wanna live in a highly populated area.
0:44
I'm a city kid so I can relate.
0:48
On the right, starting in column J,
0:50
I have a table with the ten
most populated Texas counties.
0:53
We wanna find the population of each
county along with the median cost for
0:57
two bedroom and
three bedroom rentals in each county.
1:02
It would take too long to scroll
through this list manually, and
1:06
there's a chance of making
mistakes by using copy and paste.
1:09
So let's use a lookup function instead.
1:13
There are two different types of lookups,
a VLOOKUP and an HLOOKUP.
1:16
The V in VLOOKUP stands for vertical.
1:22
So it'll do a search and
find in a column up and down.
1:25
The H in HLOOKUP stands for horizontal,
or a search and find in a row,
1:29
left and right.
1:34
Since our data is organized in columns,
1:36
let's use a VLOOKUP to search up and
down.
1:39
Let's start with the population for
Harris County, home of Houston, Texas.
1:43
In cell K2, I'll type
1:47
=VLOOKUP(,
1:52
click cell J2 and
1:57
type a comma.
2:01
Then I'll click column heading A and
drag the column H.
2:04
Type another comma, then 2.
2:12
Let's look at this formula
before we accept it.
2:15
J2 has the value to search for Harris.
2:19
A:H is the range of
cells to search through.
2:23
The first column is used
to find out search value.
2:27
2 is the column number within the range.
2:29
We want the population so
2:33
we need the value found in
the second column of the Harris row.
2:34
There is an optional argument, but
2:38
since our first column is sorted in
ascending order, we don't need it.
2:40
Close parentheses and hit Enter.
2:46
We see the population of Harris County
is over 4 million people.
2:50
If a pop up appears to autofill
the column, click the checkmark and
2:55
the V lookup formula is automatically
added to the rest of the column.
2:58
If not, that's okay, I'll show you
how to autofill the columns yourself.
3:02
In the meantime,
3:07
let's find the median cost of
a two-bedroom apartment in Harris County.
3:07
This time, I'll type the entire formula.
3:12
We're still searching for
Harris in J2, and
3:15
still searching through the same data set,
columns A through H.
3:18
This time we need the 2 BR column
which is column 1, 2, 3, 4, 5, 6.
3:23
In cell L2,
3:30
type =VLOOKUP(J2,
3:34
A:H, 6), Enter.
3:41
$952, great.
3:48
How about three bedroom
apartments in Harris County?
3:51
Still searching for
Harris in the same dataset.
3:54
3 BR is in column 7.
3:57
So in cell M2,
4:01
type =VLOOKUP(J2,
4:06
A:H,7), Enter.
4:15
$1,299.
4:24
To fill the rest of the table,
select values K2 through M2.
4:27
See the square in the bottom right corner,
this is the fill handle,
4:35
we're going to hover over it until
our cursor becomes a thin plus sign.
4:39
Then click and drag down to Fort Bend
county at the bottom of our list.
4:44
If you did this correctly, you should
have the population and cost of two and
4:54
three bedroom rentals for ten counties.
4:57
I can see at a glance that there are five
counties with a population over 1 million.
4:59
And 2-bedroom apartments are much more
expensive in Travis County than the other
5:05
counties in our list.
5:10
In the next video,
5:13
we'll apply conditional formatting to
highlight data based on specific criteria.
5:14
See you there.
5:19
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