How to Do a VLOOKUP in Excel - Video Tutorial

http://vitamincm.com/excel-vlookup-software-tutorial/
This video shows you how to use MicroSoft Excel's VLOOKUP function to compare values in two lists. It accompanies the full article on VitaminCM.com. See the full tutorial at http://vitamincm.com/excel-vlookup-software-tutorial/

---

Closed Caption:

hi this is Christmas yellow vitamin C
m.com and today we're going to talk
about using the vlookup function and
Microsoft Excel vlookup is used for is
to find a value and see whether it
exists in another range of values so i
have a list of people here that paid for
an event so all the people that paid are
here and this is the amount each of them
paid then on this side I have a list of
the people that attended so not all the
people that attended have paid so i need
to find out which people paid in which
people didn't by looking up people on
the list of people who paid
so before you start to do a vlookup the
best thing to do is create a named range
a named range just grabs a block of
cells and put a name on it allows you to
reference it and other places in the
workbook so we want to name this range
of people that already paid and one of
the things I like to do if I'm going to
keep adding to this
I like to give myself a little extra
space so I'm going to drag it down and
leave a few extra rows here now right
click and select name a range and i'll
name this range paid
ok so now i can reference that range of
cells anywhere in this work
now i want to use the vlookup formula so
i'm going to click in the cell next to
this first person's name I want to see
if she is part of this group now
obviously I can see here is a small list
but when you have potentially thousands
of items in your list
it gets a lot more tricky to figure that
out so i'll click in the cell next to
this person's name and i'll click the
insert function button here and I need
to use a lookup functions
I can search so i'll type you look up
and there's we look up there look up eh
look up eh look up does the same thing
instead of going up and down Collins it
goes left to right across rows so select
me look up and what it does
the first thing it says it explains what
the function does so it looks for value
in the leftmost column of the table over
here and then returns a value in the
same
row or a column you specify and then you
notice he click in each of these fields
here to build this function
it gives you an explanation of each of
them do so there's four fields that you
need to use here
the first one is a lookup value so what
are you looking for
want to look up this person this this
record of cell d2 the table array
I can either drag a range of cells or I
could use my named range which works a
lot better with be lookups all type and
paid you notice as I have been paid
it shows me the list of values that that
that range contains so this is where i'm
going to look I'm going to check for
this value and see if it's in this range
then column index number
this is the column number in the table
that I want to return so i have a range
that's two columns wide if i wanna if I
find a match for this Mary which is here
- I want every turn the value in column
1 which is the name or income to which
is the amount and for this activity i
want to do her name and then range
lookup range lookup there's two options
true or false false means it has to be
an exact match
true means the closest match most of the
time you will not want to use true you
want an exact match though we use balls
and we're ready to go so hit ok so it
looked for this value in this list it
found the value right here and then it
returned the data that was in the first
column of the range which is the name I
go back here and i opened the function
editor again and I say no I don't want
the name I want the amount so i'll
change it to the second column
notice that the amount pulls up so I can
change that back to
first column and now i can just drag it
all the way down my range and it will
look up everybody
so as it goes down it gives me the name
when it finds people now when I find
someone that's not in the list so Mike
we look here there's no Mike in the list
gives me an error sign pound na same
with Chris
same with becky so these are the people
who have not pay again we need to
collect from now the cool thing about it
is if I go back and I add into the empty
cells that I specified as part of the
range
so if i put in Chris and hit enter
notice it reruns the formula the
function and it says yes he is in the
list and if i put an amount here and i
change the column number 22
it would return that value
so that's how we would work with the
vlookup now there's one more extra thing
you can do to clean it up and make it a
little nicer if you notice you get these
pound and a symbols return and there's a
thing you can do you can take the
vlookup formula and nested inside of
another formula to make it look a little
more attractive
so i'm going to copy this exact formula
out of here
so we look up equals scepter
so I'm going to use the if error
function here and the if error function
returns a value if there's an error in
the expression
otherwise there's not never returns it
so what I'll do is I'll paste the
vlookup formula in here and then it if
there's an error meaning if the person
doesn't exist
I want to take the words collect so that
i know we need to collect from them so
it runs again and it looks the same
because Mary was found but if i copy the
rest of the way down
you'll notice the people that did not
exist in the list have the words collect
next to them so it makes it a little
cleaner instead of that pound and a so
that's how you use the vlookup to
compare values in two separate lists and
check whether they exist or they don't
and then return values based on some
logic day you set up