Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Intermediate Spreadsheets!
You have completed Intermediate Spreadsheets!
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