Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Intermediate Spreadsheets

Well done!

You have completed Intermediate Spreadsheets!

Sign up for Treehouse Back to Library
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

Lookup Functions

5:20 with Richard Tillies

In this video, we'll introduce you to lookup functions to find specific data points within a table.

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