How to Use VLOOKUP in Microsoft Excel 2003 2010 2013 2016 vlookup tutorial

Tom Fragale - Microsoft Certified Trainer
120 followers
1 Video View·Mar 2, 2025
For Full versions of my videos or to join my mailing list go to :
http://www.pcwebinars.com
How to Use VLOOKUP in Microsoft Excel 2003 2010 2013 2016 vlookup tutorial
Subscribe to my channel - http://www.youtube.com/user/TomFragale?sub_confirmation=1
Excel Macros Complete
https://www.youtube.com/watch?v=mRCx7x4Tyfg
Excel Pivot Tables Complete
https://www.youtube.com/watch?v=Yzk0-u0fCCE
Excel Charts
https://www.youtube.com/watch?v=nDn5thIfZD4
Top 10 Functions in Excel
https://www.youtube.com/watch?v=WxxLGJPUHKE
Access Queries
https://www.youtube.com/watch?v=T5jLS5Ofwmo&index=87&list=PLuugq1ExG0a1CYItcr_N4Ij37zg8WQLop&t=8s
in this video we're going to talk about
another very powerful function it's
called a v lookup this is one that we're
going to use all the time let's say you
show this to your boss and your boss
decides that for each department you
would like to see the department head
and also the charity that they're
they're trying to raise funds for notice
how I have that information on a
separate sheet I have a sheet that's
called Department info and the
department info sheet chose the
department the charity the charity Ras
money for and the department manager
somehow I have to pull this information
from this sheet and pull it over to
sheet
2014 first of all let's add some columns
into the sheet so I want to right click
on column C and I'll say
insert in fact I should I'll do that
again I'm going to right click on column
C and pick on insert of course
everything moves over to the right in
this case I'll have
manager and then I'll have
charity okay now how am I going to get
the information from that other sheet to
pull over into this sheet and I have to
make sure I get the proper manager and
the proper charity for that department
well the way we're going to do that is
with something that's called a vlookup
so once again you could type in the
formula for the vlookup in but we're
going to use the function Builder since
this is the basic so we're going to type
in the equal sign here and we'll pick on
this FX so think about it I'm trying to
find the manager for the sales
department and I know that's on the
other sheet so I'm going to pick on the
FX and now I know the function is called
vlookup so in this screen I'll just type
in
vlookup and then I'll click on
go and it brings me to the vlookup
formula I'm going to pick on that
function and I'll click on
okay now no how this function actually
has four different
parameters and uh the top three are
actually required and this one is
optional but we're still going to use
that the first thing it's looking for
here is the look up value in other words
what are you trying to look up well I'm
trying to look up the I'm trying to look
up the department sales so in that case
it's going to be
A5 now the table
array uh the table array I know is in
that other sheet so look what I'm going
to do I'm going to pick on table array
and I'm going to pick in the sheet it's
called Department
info and then I'm want to highlight I
don't need the header so much but I need
the data now the important part about
this table is the First Column which is
column A so that's show you where the
lookup is going to occur and by the way
notice how column A is sorted that's
usually important as well now this table
can be many many columns across and many
many rows down but really it's doing the
lookup in the First Column of that cable
it's important that you see
that uh so notice how it has the sheet
name Department info and then the
exclamation point and then the cell
references so usually when you see the
exclamation point uh that means before
the exclamation point is the sheet name
when it's on a different sheet and then
the after of the exclamation point is
the cell references now the column index
number this is important that you see
this uh let me go back to this table
over here for a second in the table this
is column one this is column two and
this is column 3 so we don't use letters
a b and c but you use relative to the
table this is column one column 2 column
3 so I want to get the manager which is
really the third row so for the column
index number I'm going to type in the
three that means when I find the
appropriate row I want to get the third
column from that
table now you see where it says range
look up let's see what it says on this
screen here it says range lookup is a
logical value find the closest match in
the First Column when it's sorting in
ascended order then you put true there
or you omit it so if you either type in
the word true or some people will type
in the number one there it means the
same thing or if you just leave it blank
that means it's going to find the
closest one uh the closest one that it
can find even though if it's not an
exact match if you really want to be an
exact match then you type in the word
false there or I've even seen some
people use the number zero which means
the same thing I'm going to type in the
word false that means it has to be an
exact
match so in this case the lookup value
is the cell that you're trying to look
up which is the word sales in column A
now I I look at that up in the table
array and so the table array happens to
be on the other sheet called Department
info and it was sells A3 through C9
the column index number means in that
table I want to find the third column
which as I recall was the manager and
then the range lookup means false I'm
going to click on okay there it means
you want to do an exact
match now let's see if that's the case
it says sales and it tell me the manager
for sales is uh Chuck massan let's see
if that's the case so if I pick on
Department info if I look at sales the
third column over 1 2 3 it does say
Chuck missan there so that is correct it
found the proper manager based on that
department so let's go back to sheet
2014 here now I want to show you
something important this is actually
going to be uh a use of the absolute
references that I talked about before in
a previous video I'm going to copy this
down with the speed
go now some of them say
na let me show you why you see I always
always wanted to look at A3 through C9 I
want that to stay absolute but when I
did a regular speed fill it changed this
one to A4 the
C10 see it's still
relative this is now A5 to C11 it's
moving one it's moving it down one cell
for each row that's a relative formula
but I really don't want that to change
so this is where we would use the dollar
signs this is an absolute reference so
here's what I'll do I'm going to click
on that cell the first one and I'll put
a dollar sign before the a and a dollar
sign before the
I'll put a dollar sign before the C and
a dollar sign before the nine that's
going to make those sell references an
absolute reference now I'll make sure
that I copy it
down okay now let's see if it worked so
for marketing is
marketing is John St Stasi the marketing
department uh
manager and he
is Jill holiday is she the manager for
the um
advertising and she you can see that she
is so now notice that when I have the
proper view look up it's taking this
information from column A looking it up
in the table in the department info
sheet finding the proper row when it
finds the F row it's it's going to get
the uh third column one two three which
is the manager's name and you can see
it's really getting the proper manager
for that department let's do another V
lookup for the uh charity so I'll say
equals pick on the FX again now notice
how the V lookup is in my most recently
used category so your most recently use
category starts to be your favorites
well I'll just pick on the V look up
again I'm still I still W look up at
that
department uh for the table array I'm
going to go back to Department info and
highlight that same exact information
that we did before except we know that
we need the dollar signs there so we
might as well just type those in we know
that going to be necessary anyway so
might as well do it right
now now on the table this time I want
the um the charity which happened to be
the second column of the table so that's
why I'm typing the number two there and
I also want these to be an exact match
so I'll type in the word false for the
range
lookup so for the lookup value I have A5
for the table array is actually still
the same table aray is before except now
I want to return the second column which
is the charity and then the range lookup
is going to be false which is an exact
match uh now let's expand column D
that's better and go ahead and drive
that
down and now based on the department for
that row it should have found the proper
manager with this vlookup and it should
have found the proper charity on this
vlookup notice how it's almost the exact
same formula the only thing that changed
is that this one has number two in the
third parameter and and this one has
number three in the third parameter
because this one's returning the third
column this one's returning the second
column from the same
row um so now I need two different
pieces of information here I need the
manager and the charity that's why I
have two different vlookups so hopefully
that gives you an idea of how to use
your vlookup formula in Microsoft Excel
