Instructor: All right, enough talking about
VLOOKUP and HLOOKUP, let's actually use it.
It's really hard to really learn these formulas
until you practice them on real data.
So let's go ahead and get some hands-on practice
using the VLOOKUP function.
Open up the section five lookup reference Excel doc
and we'll work out of there.
To recap, I've got my hitting data tab
which is organized by player, by year
with a number of hitting metrics from column C through N.
And then similarly, I have a fielding data tab
by player, by year with additional fielding metrics here.
The goal of this exercise
is going to be to pull in those fielding data metrics
into columns O through S in my hitting data tab.
So this is gonna be like my source or master data tab
that joins those two sets of metrics together in one place.
So what I can't do is just start writing a VLOOKUP function
on the player name for instance as my lookup value
because I have the data by year as well.
So if I looked up the player name
in the fielding data array,
it's gonna find the first instance of that player.
You know, if you played in 2010 for instance,
but ignore any later instances.
So if I have a player who exists in the dataset five times
because he played all five years of the data,
then that VLOOKUP function based only on player name
is going to miss four of those instances.
It's gonna find the first one,
it's gonna return the associated values
and ignore the others.
So the first step that we need to do is create a key column.
So in hitting data, I'm gonna right click
and insert a column to the left of column A.
We call it key.
And to define this function or this new dimension,
I'm gonna just write equals B2 ampersand C2.
This is called a concatenate function
and it just matches those text strings together
to create a new unique field.
So there's two things that's important here.
One, it's unique.
There's only one instance of each of these values
in this entire column.
And two, it's in the first column, column A.
That's very important
if you remember the first rule of the lookup functions.
So I'm gonna jump to fielding data
and do the exact same thing.
New column A, call it key,
and it's gonna be equal to B2 ampersand C2,
apply it down.
And now I've got my unique common key between both tabs
that I can use for my lookup.
So the next step that I like to do is just say all right,
let's jump to my lookup data and find which fields
or which variables I'm looking to pull into my master sheet
and it's gonna be these five.
So I'm just gonna paste them here as placeholders
and this is where all my VLOOKUP functions are gonna live.
So let's start with the position column P
and I'm just gonna start writing a VLOOKUP.
So equals VLOOKUP, open the parenthesis.
My lookup value is going to be whatever my key is here,
so A2.
My lookup or table array
where I am trying to find this player key name
is in the fielding data from A1
all the way over and all the way down to H12056.
And before I do anything else, I'm gonna press F4
which fixes that entire array in place
so that I can apply this lookup formula down
once I've written it
and not have this data array shift along with it.
So I'm gonna press comma, column index number
is basically the column where my position field lives
'cause that's the metric that I'm trying to populate
right now
which is in column one, two, three, four.
So column index four
and then I'm just gonna do a zero for exact match.
Close the parenthesis, hit Enter.
So now if I apply this all the way down,
as you can see it's looking up each player name.
If I press F2, dive in,
yet the table array or lookup array is not shifting.
So there you go. That's position.
Now, one other little trick that I use
to save myself some time.
So you'll notice if I just try to drag this formula
to the right,
I'm gonna get an error.
And the reason is
if you look at how the formula changes,
watch the lookup value.
First it's A2, it shifts to B2
because that was a completely relative array.
So I moved it to one column to the right,
the A changed to a B.
But in this case, no matter which column
I'm trying to pull in with a lookup function,
I'm always gonna be looking up the same key field.
So the A is never gonna change from an A.
So what I'm gonna do is press F4 three times
to just fix the column but leave the row relative.
That way I can apply this down again,
the rows will update which is what I want.
But now if I shift it over,
it stays A2 which is what I want.
Now you'll notice it's actually the same exact function
because nothing is changing from my references.
At this point,
all I need to do is change the column index number
'cause remember position was pulling in the value
in the fourth column,
put outs is in the fifth column
so I just need to change the four to a five
and there you go.
I can apply that down.
Same thing, I can drag this over,
change the five to a six to pull in assists
which live in the sixth column over
and then I'll do the same thing for errors and double plays
which live in columns seven and eight respectively.
And then grab all three of those,
double click the corner to apply them down.
So there you go.
I've written a lookup function to pull in my fielding data
into one master source tab.