hello there and welcome to excel campus
my name is John Acampora and in this
video I'm going to explain everything
you need to know to get started with
vlookup
so by the end of the video you're going
to be writing your own vlookup formulas
and you'll also know what causes some of
those common errors that can be so
frustrating and how to prevent them. Now
I've designed this video to be a follow
along lesson you can download the excel
file that I use during the video
there'll be a link in the description
below the video there we can download
the file and follow along and practice
you don't have to do this but I highly
encourage it it'll really help you learn
faster so go ahead and pause the video
now and download that file if you'd like
and we'll jump over to excel and get
started alright so I'm here in the
practice file on the overview sheet and
here's a list of the things we're going
to learn today so I'm first going to
give a definition and show some common
uses for vlookup you can then follow
along and we'll have a look at how to
write a vlookup formula I'll explain the
two main causes of errors for vlookups
and how we can fix those I'll also
explain why vlookup stops at the first
match and the sorting myth as well and
then we'll look at how we can create
relationships between tables with a
vlookup formula and this video will
definitely be packed with tips and
shortcuts to help make this process
faster and easier so let's go over to
the vlookup definition sheet and here I
have a simple definition of vlookup and
the whole point the purpose of vlookup
is to look for a value in a column and
return a result from a cell in a row
where a match is found and this allows
us to answer questions about our data so
I have this menu example here this is a
popular example I use our analogy for
vlookup with this Starbucks menu if
you've ever ordered food off of any menu
then you've actually done a vlookup in
your head and what I mean by that is the
whole point of vlookup is to scan down a
column until it finds an item let's say
we want to find the price of the cafe
mocha and then once we find that item we
go over to the right to find the price
and a different column and that's
exactly what vlookup does and it always
looks to the right and of course we'll
learn more about this as we go but
that's just one simple analogy to help
you remember what a vlookup does and
within excel there's tons of uses for
vlookup it's of
very powerful tool first of all like I
said we can create relationships between
data and look-up tables like we have
here for pulling in data from some
look-up tables we can use vlookup for
that we can also use it to search
databases and tables to return
information based on a name that we're
looking up we can use it for interactive
reports and financial models maybe we
have a drop-down here where we're
changing the input and then that's
returning different results so we can
use vlookup to drive that and we can
also use vlookup for tax and Commission
rate calculations so instead of using
some complex nested-if formula like we
have here we can use a simple vlookup to
look up values within a table like this
and return a result so there definitely
a lot of uses for vlookup even more than
I've explained here but let's jump over
to the right a vlookup sheet and write
our first vlookup formula
so I'm here on the right a vlookup sheet
and we're going to write a vlookup
formula and we want to answer a question
which is what is the price of a cafe
mocha sized Grande now we have our
Starbucks menu up here and of course we
could just scan down that column a until
we find cafe mocha and then scan over to
the right to the grande paalam to see
this price of 395 and that's exactly
what we want to return here in this cell
and sell B 15 with a vlookup formula so
go ahead and select cell B 15 I'm going
to type equals and then the word vlookup
and as you start typing vlookup you'll
see it narrowed down here in the drop
down we can just hit tab on the keyboard
to autofill that and then we'll see the
four arguments for the vlookup formula
so we have the lookup value the table
array the column index number and range
lookup now this is the first time you're
seeing vlookup those can be a bit
confusing so I've also provided a simple
definition here of those arguments and
the first one is the value that we want
to look for so in this case we're just
going to select this cell here that
contains the word cafe mocha so that'll
put a reference to cell a 15 in our
formula and then we'll go ahead and type
a comma and the next argument is the
table array so this is the range that we
want to look in so we're going to select
our entire range or menu here in this
case which would be from cell a4 all the
way to D 11 and that will create the
reference here in our formula now one
tip at this point right here is to hit
f4 on the keyboard and that will create
an absolute reference so if we were to
copy this formula down our table array
range will not move now this is not
required and I'll talk more about this
when we talk about errors but it's just
a good habit to get into if you're using
a laptop or a smaller keyboard you might
need to hold the function key when you
press the f4 key so so far we have the
first two arguments and it's important
to point out that vlookup is always
going to look in the first column of the
table array so we're looking for this
value and cell a 15 and vlookup will
always just look in this first
and look down this first column
vertically and that's what the V and
vlookup stands for is vertical so it's
going to look vertically down this first
column here it's not actually going to
look for that value in any of the other
columns the reason we select those other
columns is because we're now going to
specify the column that we want to
return a value from and that's the
column index number or the column number
of the value to return so in this case
here we want a Grande which is the third
column the lookup starts here this would
be column 1 of our table array tall
would be column 2 grande is column 3 and
venti is column 4 so we want column 3 so
we're going to type a comma and then
we'll just type the number 3 here for
the third argument and then we'll go
ahead and type another comma and the 4th
argument is the range lookup now that's
probably the most confusing of all of
them but this is either an approximate
or an exact match now 99% of the time
when you're starting out with vlookup
you're going to put false here for an
exact match we can use it approximate
matches when we're looking up numbers
for Commission or tax rate calculations
but for the most part we want to specify
a false here so it's best to just get in
the habit of typing false of course you
can select this and tab into it to put a
false here for the last argument and now
that we have all four arguments go ahead
and close the parentheses and then hit
enter and that will do the lookup and
return the price of 395 now if you do
have the practice file open at this
point I encourage you to pause the video
and write out the formula if you haven't
already this will of course help you
practice and really learn how to write
vlookups so I just want to take a quick
break and ask a favor of you if you're
enjoying this video please subscribe to
our Channel there's a big red button
below this video they can click to
subscribe and also get notified when new
videos are published and we also have a
free weekly email newsletter that
contains tips and tutorials just like
this that will help you learn Excel so
I'll put a link in the description below
this video as well where you can get
plugged in with that thanks again and
now let's get back to the training so
we're
going to look at the two main causes of
heirs with vlookups I'm here on the
heirs sheet and we're getting going to
do some V lookups on our menu and we're
going to answer this question which is
how much will this order cost for size
grandes so we have an order here maybe
from some co-workers and we're going to
look up all of those values return the
prices and then total it up but we might
get some errors while we do this so
let's go ahead and write a vlookup again
right here in this cell B 16 equals
vlookup will tab into that this is going
to be our lookup value I'm going to type
a comma here and then select our table
array now I'm not going to make this an
absolute reference this time like I said
before you can hit the f4 key right here
but I'm just going to pretend like I
forgot that step I'll type a3 here for
our column index number to return that
third column comma and then false for an
exact match close the parentheses there
and we'll go ahead and hit enter
so that'll return our price of 415 which
is great but now if we were to copy this
formula down
we're going to get some errors and you
can see right here we're getting a hit
an air and cell b-17
if I hit f2 on the keyboard to jump into
this and edit this formula we can see
that our table array this range here has
moved down it's moved down to row five
from row five to row 12 and that
happened when I copied the formula down
and the lookup value cafe latte is
outside of that range we can see it's up
here in Row 4 so vlookup is not going to
find this value because it's outside of
the table all right so I'll hit escape
now and that's really the cause of this
n/a air for the most part is that
vlookup can't find that lookup value so
let's jump back up here to sell B 16 hit
f2 again I'm going to select my table
array so just select the text here you
can also use a little screen the
shortcut here if you click the screen
tip that will also select this text
right here and then we'll hit f4 on the
keyboard that will create the absolute
reference
so now I'll hit enter again we have this
formula and I'm going to copy it down
and now we get a result here hit f2 here
we can now see that we have the
absolute reference this range is
anchored down and it's not moving as I
copy the formula down so when you're
starting out that's probably the most
common cause for errors is you just
forget to make this an absolute
reference the table array an absolute
reference copy the formula down and
you're going to get some errors now
another common cause of errors is that
the lookup value doesn't exist in the
table array and that's exactly what's
happening here one of our co-workers
decided to order a light beer from
Starbucks might be a little too early
for that
so unfortunately this is not going to
return a result here because light beer
is not in this list it's nowhere in this
list so vlookup is going to return in
air now we can't handle those errors
with the if air function and I'll talk
about that in a separate video but to
fix this for now let's just change this
to an item that is in the menu so maybe
that will give them a caramel macchiato
instead just going to hit ctrl C to copy
that and then ctrl V to paste it right
here and now you can see we're getting
the result of the price for the size
grande and then finally down here we're
still getting an air for this cafe mocha
and of course cafe mocha this term or
this word is in our table array so why
is this happening
well another common reason is that
there's a space at the end of this
phrase so if I just double click into
the cell or hit f2 you can see the text
cursor blinking right there and there's
actually an extra space at the end of
the phrase or the end of the text and
that's causing this to not be an exact
match so extra spaces in your text
whether they're in the lookup formula
here or in the table array you could
have spaces at the end of these words as
well that's going to cause a mismatch in
the lookup value and return that air so
of course we need to fix that there's
many ways to do that but for right now
I'm just going to delete that extra
space and hit enter and now we can see
we get our result right here so that
should help you with the most common
types of errors when you're starting out
with vlookup because of course it can be
frustrating when you get errors but that
will help you resolve the majority of
them so now we just want to get a quick
total here we can select cell B 20
use the autosum function if we go to the
Home tab of the ribbon and then we go
right here to the autosum button we can
just click that keyboard shortcut is alt
equal sign hold down the Alt key and
then press the equal sign that'll
automatically give us this sum formula
just hit enter and we'll get our result
here we need $15.80 to take the
starbucks to place this order so again I
encourage you to pause at this point
practice writing those formulas and
fixing those airs
so we're not going to look at how
vlookup stops at the first match and the
myth with sorting your data so I'm here
on the first match and sorting sheet and
I have the vlookup formula written here
in cell b16 I'll just hit f2 on the
keyboard to jump into this cell and the
first thing that's important to know is
that vlookup is always going to stop
when it finds the first match so again
here in this column it's looking for the
word cafe mocha and column a and we can
see now that there's actually two
occurrences of cafe mocha however
vlookup is always going to start at the
top and look down vertically and stop
whenever it finds the first match so
it's going to stop here go over three
columns and return the price of 395 it's
never going to find this occurrence down
here even though this has a cheaper
price and things like that
it's never going to find this because
vlookup always stops at the first match
and that kind of leads to my next point
about sorting your data I commonly hear
this myth that you always need to sort
your data in ascending order before
writing a vlookup formula or in order
for a vlookup formula to work and that's
not true
at least not when the last argument is
false for an exact match as we can see
here our data is not in ascending order
we have a W before a C and the formula
is still working now if you are looking
up numbers for Commission rate or tax
calculations and you have the last
argument as true in that case you would
want to sort your data however like I
said before especially when we're
starting out with vlookup the majority
of the time we're going to look for an
exact match and have this last argument
as false and in those cases you do not
need to take the extra step of sorting
your data now one caveat to that is if
in this case here you did want to return
the cheapest price you might want to
sort these price columns in order for
this row to be above this row here
because vlookup will always stop at the
first match however again it's not
always required so just know that you
don't always have to take that extra
step to sort your data
so we're now going to look at how to
create relationships between tables and
write V lookups to look up data onto
other sheets so I'm here on the
relationships tab and we have some sales
data here in this sheet and we want to
bring in some data from some look-up
tables over here on this lookups look-up
tables sheet so we have some look-up
tables over here and we have a list of
product names and the categories that
those are grouped in here we have a list
of salespeople and some information
about the salespeople including the
region they're in and over here we have
customer IDs and customer names so we
have some look-up tables over here I'll
jump back over to the relationship sheet
and we're going to use a vlookup formula
to pull in that information so in this
first example here we want to look up
the product name and return the product
category from those look-up tables so
this will be some good practice for us
we're just going to type vlookup tab
into that our lookup value will be this
product name here type a comma and then
now we're going to jump over to the
lookup tables sheet and we're just going
to again select our lookup range I'm
sorry our table array or a lookup range
so we'll just select that there you can
see now up here in the formula bar that
I have the reference to the sheet name
and then the range reference so right
here again I can hit f4 on the keyboard
to make that an absolute reference going
to go ahead and type a comma and our
column index number is going to be two
for the second column so we'll type a
two there you can type a comma and then
type false for an exact match close the
parentheses there and then when we go
ahead and hit enter that'll take us back
to the relationship sheet and show the
result right here so now we can copy
this formula down just select the cell
double click the fill handle and that
will copy our formula down now one thing
you can do one little tip here is if you
have a lot of data a lot of rows and you
want to check for errors if you have the
filters turned on you can just click the
filter drop down menu here go to the
bottom of the list and we can see we do
have some n/a air values here in the
sheet so this is a good way to go
research those
this uncheck select all check in a hit
okay and that will filter down the rows
for just our na airs and now we can see
the reason here is that we don't have a
product name in these cells we also have
a dollar revenue of a dollar so this
might be something weird that we need to
go investigate to fill in a product name
here or potentially we want to handle
this with an if air statement and again
we'll talk about that in a future video
so I'm going to go ahead and clear the
filter there we'll just leave those airs
for now and we're going to do the same
thing in this reps region column by
writing the vlookup formula I'm going to
explain another little tip here with a
screen tip so again start type let's
type writing the vlookup our lookup
value this time is going to be our
salesperson type a comma there I'm going
to go over to our lookup tables sheet
and now we're going to select this range
here now when you're look up table has
or your table array has a lot of columns
sometimes it can be difficult to figure
out which column index number we need to
use however there's a little screen tip
there that appears in the bottom right
of our selection that says eight are by
seven C and that 7c tells us that this
is the seventh column that we've
selected if we move over here we can see
that changes to 6c for the sixth column
so this lets us know that for our region
we want to return these we want to
specify the seventh column in order to
return the region column so that's just
a nice little tip there makes it really
easy especially if you have dozens or
even hundreds of columns within your
data set and again I'll hit f4 to anchor
that down then go up here type a comma
we're going to type a seven here for
that seventh column it's also important
to point out that that is the seventh
column of the range or the table right
so that's a seventh column of our table
right here it's not the seventh column
of the sheet column K would not be the
seventh column I believe would be the
11th column of the sheet but we want the
seventh column of the table array that
we have selected so that's important to
note there that column index number is
relative to the table right comma will
type false for an exact match
close the parentheses there and hit
enter and that will return our region
right here again I'll just double click
the fill handle to copy that down
quickly check to see if we have any
errors we do not so everything looks
good there and we've now created a
relationship between this data table and
the look-up tables and when I say
relationship what I really mean is that
this allows us to group our data so to
bring in some metadata group our data
and then if we were doing summary
reports or pivot tables we could then
quickly create a summary report based on
the region so maybe some of sales by
region we could create a report for that
now that we've brought their region in
here to our data table so this allows us
to group data and kind of fill in the
gaps of data that we do not have in our
original data sets and I do have a whole
nother video series on pivot tables and
dashboards if you're interested in
learning how to quickly summarize your
data and create interactive charts and
dashboards all right so I hope this
video has helped to get started with
vlookup I know it can feel scary and
overwhelming at first but I highly
encourage you to practice even go back
and re-watch this video and it will
definitely get easier over time now to
help you learn even more
I've prepared a bonus challenge file for
you this will help test your skills and
learn even more techniques and uses for
vlookup and I also have a bonus video
that walks through some solutions and I
share additional tips and tricks to
learn vlookup so I'll put a link to that
in the description below this video
definitely download that and check that
out now if you have any questions please
feel free to leave a comment below this
video and we'd also love to know what
you're going to use vlookup for now that
you know how to use it so leave a
comment below with that answer as well
thanks again for joining me and I'll see
you in the next video have a great day
bye
