alright hello everybody this is Maxwell
Stringham your trusty tech trainer here
to help you out with some vlookup
functionalities specifically how to use
vlookup when you are using more than one
Microsoft Excel workbook in order to
download and follow along by utilizing
the Excel workbooks that I'll be showing
you today in this video you can go ahead
and go to this shortened URL and
download the two Excel workbooks that
are in the Google Drive folder that is
available through this link it's again
this is a case sensitive shortened URL
so anything that's capitalized must be
capitalized anything this lower case
must be lowercase we'll be using these
two files at this abbreviation for
employee deductions and this one for job
salary records so with that let's get
going now we have two folders or two
Excel workbooks here we have job salary
records and we have this employee
deductions worksheet now with these two
whenever I'm working with - oops excuse
me whenever I'm working with two Excel
workbooks I like to be able to see them
both side-by-side so first step that
we're going to want to do is click view
and then click arrange all after we do
that we go to vertical and then click OK
now what that did is that just arranged
the two Excel workbooks side-by-side
with the to arrange them side-by-side
into vertical columns now to go ahead
and get started on our goal our
objective is to create a new column
titled go ahead and type it in here it
is going to be titled annual salary and
this is in column o of the employee
deductions workbook now in order to
populate this we will use the vlookup
functionality so in order to get started
with our V
lookup function we need to go ahead and
start with an equal sign as we always
use an equal sign to start any function
in excel so equal sign first step then I
type the lookup vlookup stands for value
lookup now Y value lookup well because
the first step of this function is
because we are looking up a value this
here that pops up underneath is sort of
like a display or a legend of how the
vlookup function works so lookup value
now what we are going to indicate for
this first field of our function is what
value do we want to look up well we want
john smith's annual salary now we can't
just look up his salary straight up but
we know that his salary is listed in
another folder in this other workbook
and that his salary is listed relative
to his employee ID number so what we're
going to do is look up his employee ID
number in the other workbook so I
selected John Smith's employee ID number
a two as I am working in Row two and I
want to populate this part of this
column Row two of this column so a 2
comma two move on to the next step
table array now table array basically
this is the part of the function where
we tell the computer hey go ahead and
look in this array or this region of the
table for the value that I just told you
to look up so we don't want to look in
this same workbook this is the employee
duck deductions workbook where we want
it to wind up instead we're actually
going to click over here and activate
this one we want to highlight or not
highlight select this entire array of
this table all the values in this table
as this includes all of those employee
ID number
and also includes the annual salaries
that are relative to those employee ID
numbers now I click back over to this
sheet and I can see here that the job
salary records excel sheet is titled in
the single parentheses and then the
exclamation mark finishes the title of
that worksheet basically that's the part
where it tells the workbook hey or
that's the part of that function excuse
me
within this part of the function is
basically saying before that exclamation
mark hey go over to this other file pull
the array from there this right here is
the relative coordinates of that array
now we hit comma to move on to the next
column index number now the column index
number is basically going this is where
we indicate in our function where within
the array that we just selected so again
within the array that we just selected
within which column of that array is the
data that I want to pull so one last
time within the array that I just
selected in which column of that array
is the data that I want to actually pull
now we already know that we want to
match it to the employee ID number that
we have listed in 82 of this file we
know the arrays now we know the array
selected now now column index number so
we jump over to here to pick our column
now unfortunately in Excel columns
aren't actually already indexed by
number columns are indexed by letter so
this may seem a little problematic but
Excel makes this easy on us by simply
making it so that a would be indexed as
a 1 B as a 2 C as a 3 D as a 4 and so on
so forth
in intervals of counting up by one so
with that we would just count over 1 2 3
4 5 6
seven eight and nine so column I if it
were indexed as a number would be
indexed as number nine column number
nine so I go back over to here and hit
the number nine then I go ahead and put
a comma because I'm finished with that
part of my vlookup function now this is
the final and easiest part of the
function if we select true approximate
match then what this does is basically
is telling the function hey I don't
really care if the match of the data you
pull is exact it can just be approximate
now we don't want that again if you
select true approximate match it might
just give you some rounded numbers some
numbers that are a little bit off but we
don't want that we want false exact
match so in order to select that we need
to double-click false exact match and
then I hit the parentheses close the
parentheses brace and hit the enter key
and voila just like that this cell has
now been populated with John Smith's
salary of fifty four thousand four
hundred fifty four now in order to apply
this function in a relative manner to
the rest of the people on or in this
sheet we can go ahead and apply it to
them by placing our cursor over the
corner of the bottom right corner of
this cell that we have selected so again
select the cell that already has this
function already written out in it place
the cursor over the bottom-right corner
so that the cursor indicator turns to a
solid black plus sign and then click and
hold and drag it all the way down to the
bottom of where you want it to be or the
bottom of where you want to finish
applying this function in a relative
manner now when I say applying it in a
relative manner
that means is that everything that how I
indicated in this guy John Smith I said
to look at a 2 for his employee ID
number look in column a of this row of
Row 2 then I'm saying all right then for
this guy who's down in row 73 I'm saying
well go look at column a of row 73 as
you can see here in the vlookup up top
same thing goes the array is still the
same the table array is still selected
the same and the column index number is
still the same because I'm still pulling
his salary information from the same
part of the same excuse me the same
column of the other table and I am also
still indicating false because I want
that value to be exact so with that we
are almost finished as you can see these
values are not formatted the same as the
number values over on this other table
to the right so in order to fix that we
can select them by again placing our
right cursor or placing our cursor over
the bottom-right corner of the selected
cell and Reese electing all of these
again and then we go to Home tab click
number two change our number formatting
this is where we go to format numbers
and we can choose dollar signs percents
etc however we don't want to just do
dollars we don't just want to set this
as a money format because this is not
just formatted as dollars and cents
instead we want to make sure that we are
exactly the same so we go to again that
was a number and then under the number
tab here we go to the number category
for number formatting
then decimal places we choose to show
two decimal places or to show
the decimal two places to the left of
the last digit or integer to show the
hundreds
I always show hundreds and then select
to use the thousands separator with a
comma click OK and now voila we are
finished it is exactly the same every
person salary has been populated in this
list exactly the same as they are in
this list however it's not just a simple
copy and paste they have been populated
based on the vlookup formula which look
them up relative to their employee ID
number that is all if you have any
questions please do contact me in the
comment section below and I am always
here to help
you