**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!

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 up