so a few months after publishing this
course a student messaged me and pointed
out there's actually a mistake in the
lookup exercise section and he's
absolutely right and I'll show you
what's going on here if you go into the
fielding data tab in the lookup
reference workbook you'll actually see
that there are multiple instances of
certain player keys and the reason that
happens is because certain players may
have played in multiple positions even
in the same year so for instance bobby
abreu in rows 4 through 6 is registered
as a left fielder an outfielder and a
right fielder all generating the same
key because position isn't included what
that means is that our lookup function
is searching for his player ideas player
key stopping at roe for and returning
the put outs assist errors and double
plays associated with the first instance
of the key which returns no data and if
we jump to the hitting tab Bobby Abreu
is right here in row 4 and as you can
see he's got zeros across the board even
though that year in 2010 he had 242 put
out seven assists six errors in two
double plays that data is invisible to
the vlookup because now we're dealing
with a one-to-many relationship we've
got one key in our hitting data and
three keys here in our fielding data so
how do we deal with this one option
would be to actually transform this
fielding data set and aggregate it at
the player in your level in other words
remove the position field and aggregate
or some the metrics and columns ethroo H
but there's an easier way and it's
actually to use the sum ifs function
that we covered in the stats formula
section of the course so let's jump back
to the hitting tab I'm going to copy the
column headers and Q through t paste
them starting in column you and I'm
going to give them a different color
just so that i can maintain my look up
match functions over here and compare
them side by side against this new
approach using some ifs so i'll start by
typing the sun this formula equals some
ifs now if you recall the first piece
the first
ponent of the summit's function is the
sum range what's the data that you're
trying to actually some here in this
case it's my fielding data column e
which is put outs you leave that
reference relative and I'll show you why
in a minute the second component is the
criteria range so where am I looking to
meet some criteria well it's the player
key which is column a I'll press f4 to
lock that column in and then the last
piece criteria one what criteria does
column a need to meet and that's going
back to my hitting data player key which
is in cell a2 in this case I'm going to
fix just the a just the column reference
so that my rose can shift down as I
apply this formula down so there you
have it if I press ENTER and apply it
down now that is calculated you'll see a
lot of matching values with some
exceptions so one exception is bobby
abreu in row 4 where we had been
recording 0 put outs with the vlookup
now we're recording 242 because we're
essentially summing all of the values in
column e where column a equals abreu be
0 0 1 2010 of which there are three
instances similar case with the next
player there are three instances of his
name as well in his case he generated 16
put outs at second base 11 at third base
and 16 at shortstop which totals forty
three put outs with the vlookup approach
we captured 16 which is only the first
instance but now that we've shifted to a
sum ifs approach we're capturing all of
those put outs all 43 and because I set
my reference types properly what I can
do now is even just shift this to the
remaining columns since I know these
columns are sequential and that allows
me to basically just allow that column e
to shift to F G and H and I can just
grab those cells double-click and apply
them down so there you have it we've
accounted for the fact that we no longer
had a one-to-one relation
ship with our key and we've achieved the
results that we're looking for using a
statistical function instead now the one
caveat i will make is that this worked
because we're trying to return numerical
values here which some ifs can
understand if instead we're trying to
return position for instance which is a
text field there's no way I could have
used a summits to accomplish this
because Excel doesn't know how to add p
and 2b and LF or any other text string
so again if you're working with values
sometimes you can use vlookup or some
ifs interchangeably other times you'll
need to use just one or the other