Now 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.

Before we create the VLOOKUP, we need to make sure our data is formatted properly.

What I mean by that is that if we're going to do a VLOOKUP,

we need to make sure our data is organized in columns since a VLOOKUP

does a vertical search in a column.

So you can see here on our spreadsheet that our data

is nicely formatted in columns with the row at the top for

headers, county, state, and then the number of bedrooms across.

Now we want to pull over the number of housing units by county

to our rental price tab.

First, let's label column H as Housing Units.

After we do that, we can select the cell below the header.

The first cell we want to see the new data in and

then go up to the Formulas tab, Lookup and Reference.

And then at the very bottom, you'll see VLOOKUP, select that.

Now you will notice a box pops up with four blanks.

These are the four input areas for the VLOOKUP formula.

The first is the Lookup_value, which is the value you are looking for.

In this case, it would be the county which is what we're using to match the data from

one tab to the next.

So for row two, we want to return the number of housing units for

Anderson County so we would select cell A2.

The second is the Table_array, where the range you want to search in,

which in our example is on the Population & Units tab.

When selecting the range, the Lookup_value must be in the farthest left column.

So county or column A will be our farthest left column.

The data you want to retrieve can then appear in any column to the right.

We want to return housing units so

the range we would select is columns A through C.

One thing I want to mention here, in the example, the range I selected included

entire columns, meaning all the way down to the bottom of the spreadsheet.

Now if we decide not to choose entire columns for

the range, we need to make the range an absolute reference.

On a PC, you can do this by placing your cursor in the range selection in

the formula bar or VLOOKUP box in hitting the F4 key.

For more on absolute references including how to do this on a Mac,

please see the teachers' notes.

The absolute reference will make sure the range is static.

So when you drag the formula down, it won't change the placement of your range.

I usually just play it safe and select the entire columns for the range.

The next parameter is the Col_index_num,

which is the number of the column that you want to return from your range.

So in our example, we are wanting to return Housing Units,

which is the third column over in our range.

So we'll type in the number 3.

One important thing to remember here is that VLOOKUPs only look right,

meaning you can't put in a negative column index number for

it to search columns to the left.

The last input is the Range_lookup.

This one is optional.

You can either put false, which returns an exact match, or if you put true or

leave blank, it will return an approximate match.

It's best to use false when working with unique identifiers.

In this case, make sure the county names match.

And you'll want to use true in cases where you're not matching on a unique ID.

But you're looking for the best match or

best category in cases where there are no exact matches.

If you decide to use approximate match,

your data must be sorted in ascending order, where you make incorrect results.

Once we've populated the four boxes, hit OK, and you'll see the result.

Now we can see that Anderson County has 20,116 housing units.

Let's check to make sure that is correct.

There's Anderson County and 20,116, so it worked.

So to recap, the VLOOKUP search for Anderson County in the range we specified,

and it returned whatever was in the third column on the same row is Anderson County

on our Population & Units tab.

Now we can simply drag this formula down, we'll double click and

you'll see the formula copies all the way down to the bottom.

Now let's say we also want to see what the population of each county is.

We can also pull this over from the other tab by using a VLOOKUP.

But this time instead of choosing VLOOKUP from the formula tab,

let's try typing in the formula, which is the way I usually do it.

The first thing we wanna do is label column I as Population.

And then we can place our cursor in cell I2 and type =vlookup( and

then you'll see a pop up that helps you remember the four parts of the formula.

Remember for the lookup value, we want to select county,

so you can do that by selecting it with your mouse or you can type in A2.

Then put a comma, you'll put commas in between each command and

now choose your table array which is the range you want to search in.

So go over to Population & Units tab and

select all four columns this time since we are wanting to get population.

Then another comma, and this time we will type in 4 as the column index since

the population is the fourth column in a range.

And then put another comma and type in or choose false and

then close parenthesis and hit Enter.

Now, you will see the population for Anderson County is 58.458.

Now, you can copy this down by double-clicking.

Now that we've walked through a VLOOKUP, why don't you try and

create an HLOOKUP by adding the population to the tab labeled HLOOKUP- Median Rental

$ by pulling from the tab labeled HLOOKUP- Population & Units.

Remember that HLOOKUP is just like a VLOOKUP but

instead searches horizontal rows instead of vertical columns.

After you've tried on your own,

you can meet me in the next video and I'll show you how I did it.