- Okay, so if you're using Google Sheets,
you may have two different tables
that you want to combine together.
But those tables don't necessarily
have the same column headers
and the same type of data in them.
But as long as you have something in common
between the two tables, you can merge them together.
And that's what we're going to do right now.
(upbeat music)
So the scenario that we're going to walk through
is that you work at a company,
you're in the accounting department.
So we have this blank table here
that we're going to try to fill up.
And we need to combine the information from human resources.
So the table on the left has employee IDs,
and that's what we're going to use,
because those are in both tables.
And it also has the number of vacation hours
that they didn't use, let's say at the end of December.
You need to take those vacation hours,
but you need to multiply them by the pay rate.
And then we're going to pull in the department too,
that information's in this different table.
These tables have different headers
and different numbers of people in them.
So let's come over to the accounting,
what we wanna end up with is a list just with these names,
'cause these are the only people
that we're saying have accrued vacation right here.
And we're going to bring them in to this table to the right.
So the first thing we wanna do, let's do employee ID.
That's kind of the key, if you will,
between these two tables,
because it's the same in both.
And since this is going to be the same number of rows
from the table that we wanna pull it from.
So we wanna pull it from this table.
And we're just gonna bring it in the same order.
All you really need to do for this first action
is just to hit your equals key and do a cell reference.
So we want to pull this from B6.
Left click on it with your mouse, hit Enter,
and you've pulled the first one over already.
So there's nothing tricky at this point.
Let's pull this all the way down.
So go in the lower right hand corner of the cell,
left click the mouse button,
pull it down.
All right, so we're moving along already,
we have the employee ID.
And the next one is actually the same thing too.
So we'll go through that a little bit faster,
everything's still in the same order, employee ID and hours.
But now things get a little bit trickier.
So the dollars per hour has to come from this other table.
There is the employee ID in common.
So that's what we're going to use.
But you have to use a function called VLOOKUP,
and it just stands for vertical lookup.
And you do that by saying,
hey, look up the ID 26988 in this table.
So look down for 26988.
When you find it, we're going to say, go one to the right,
and return what's there.
So we're looking to return $18.99 in this case.
So let's go into cell M6,
and we're going to type VLOOKUP.
Start your parentheses, and it will give you some help.
It's asking for the search key,
which is just what you're using to look up the value
in the other table.
So that's K6.
And then it wants to know the range.
So we're going to not select the headers,
we're going to select the date in this table,
because that's where you want to look for it.
But you'll notice that I'm not selecting the department.
I selected the data so that the key
that we have in common is all the way to the left,
and then the rest of the data is to the right.
So I only needed these two columns.
Another thing you have to remember,
I would press your f4 key right now to fix this table.
So when you drag your VLOOKUP down
into row 7, 8, 9, et cetera,
that table range stays the same.
So that's called the fixed reference.
And if you wanna type it in,
you would just add these dollar signs
that you see in the formula.
The next parameter that VLOOKUP needs is the index,
which just means, what's the column number?
So the employee ID is in column one.
And the pay per hour is in column two.
So you don't say H and I, which are the letters,
you say, one is the first one,
but we want two,
so you're counting from the left to the right.
The next parameter that needs is, is it sorted?
That's asking if the list that I'm using to look it up in.
So the range from H to I, is that sorted?
The answer is no.
So we're going to say FALSE.
And then let's close off the function.
We were looking for $18.99.
And that's what happened.
So the VLOOKUP looked up employee ID 26988,
went to the second column, $18.99, and returned that.
So let's drag this formula down.
And let's look up 38324.
That's $32.87.
So you know this formula is working.
If you get a pound NA! ERROR in here,
it means that you're trying
to look up something that's not in this table.
So I'll link to a video about how to deal with that error.
But it's basically telling you,
I tried to look it up and there's nothing there.
Our next column in this case is just some multiplication.
So we're going to multiply the hours times the rate.
That one's easy.
Hopefully, you can get through that one.
Drag that down.
But then the last thing that we're going to do
is we want to look up the department.
But when you do the VLOOKUP, so let's start it.
You know what the search key is, so let's select that.
That's okay so far.
So the problem that you run into now
is that the value that you need to look up
should be in the leftmost column,
and then you need to look to the right
to find what you're going to return.
But in this case,
the value that we want to look up is in the middle,
and then we want to return the department.
So what you do here is you kind of trick Google Sheets
a little bit, you feed it the range in a way that will shift
the employee ID, in this case, column H to the left.
So what we want to do,
you wanna start out this range with a curly brace,
which is telling Google Sheets, this is an array,
not just a regular range.
And then we're going to specify H and I first.
You can hit your dollar sign to fix it.
And then use a comma.
It maybe a semicolon if you're in a country
that uses commas for decimal separators.
But we're gonna use a comma.
And then select column G as your next column,
fix that with an f4.
And then in the curly braces.
So what you've done here
is you've given Google Sheets these ranges
in a way that it makes column H the first column,
then I, then column G.
So you're going to be able to look through this
from the left to the right,
because you took the columns and you rearranged them.
And then it wants the index numbers.
So we're going to return the third column.
In our data, it's still not in order.
So we'll end it with a FALSE.
Close it off with the parentheses, hit Enter.
And let's see.
I think that one worked, 26988.
Let's look over here, 26988 is Production.
Pull this down to copy it.
And there we go.
So we've pulled in data from a table using VLOOKUP,
but looking to left instead of to the right.
So we've put that table together now.
Your next step might be to sum the data
based on the department.
A really quick and easy way to do that
would be to make a pivot table.
This next video is going to show you how you would do that.
Thanks for watching.
If you want to see more videos like this, please subscribe.