hi there this is Vadim Mikhailenko and in
this quick video we're going to look at the
VLOOKUP formula in Microsoft Excel
We'll start with the quick overview of
the formula and then we'll look at a lot
of examples
So stay here with me on this channel
[Music]
We will start was the quick example in this
example we need to find order amount
based on the known order ad and we have
a table of existing orders to do this
all type equals vlookup and then provide
four values first value is the known
value that we're searching for in our
case this is cell A7 was the order ID
number 839 293 second value is the range
in which we'll be searching third value
is the column index for the column that
we need in our case this is column D
which contains amount but numerically
this is column number four because order
ID is column number one name is column
number two email is three and an amount
this column number four and then we'll
provide the last value false for exact
match once we finalize the formula you
see that the value found is 423 dollars
and 38 cents and as you can see this is
the amount for the order 839 293 the
vlookup function in Excel performs a
vertical lookup by searching for a value
in the first column of the table and
returning the value in the same row
based on the index number position for
example if your table contains list of
orders you can find customer name email
amount ship date or other available
attributes of the order based on a known
order ID for example if your order ID is
8:30 9:00 to 8:00 7:00
you can run look up to find customer
name which would be Ivana Weber or email
which would be Ivana Weber at trade
enter calm or find the order amount in
amount of four hundred and six dollars
and 92 cents which was shipped on
September 7th 2018 question what is the
most important object in Microsoft Excel
for Whitewater's your right to advil cup
function you need to navigate to
formulas tab and then click on an insert
function button then you type vlookup
and find the formula there are four
values we need to provide lookup value
is the known value we'll be searching
for table array is the area which will
be searched lookup index number is the
index of the column which will be
returned that contains the value we're
looking for and then range specifies the
way we'll be searching false indicates
exact match and true would be not exact
match will be using false let's add
vlookup formula using this functions
arguments dialog box for the lookup
value will provide a 7 for table array
we'll select the range between 82 and E
51
for the column index number since we're
looking for amount we'll select four and
for range lookup will select false for
exact match and you can see that we've
selected order eight three nine two nine
three and the amount for this order is
423 dollars and 38 cents the hookup
formula in Microsoft Excel helps you
find specific value based on the one on
well for example we're looking at the
list of orders in this table we have
already named email amount and should
date in our business case is we need to
find customer email based on a specific
order number or we need to find order
amount based on a specific order number
using vlookup formula we can supply
order ID and find any of the remaining
attributes here listed here in this
table this is the syntax of vlookup
formula you need to supply known value
in our case it would be order ID then
you need to supply range for searching
and you need to identify that then you
need to specify the column we're
searching for because different column
represents different value and then you
need to define the match type how
exactly are you gonna match the value
now as we know the syntax let's use
vlookup formula to find email address of
the customer for the order eight three
nine two eight four to do that I'm gonna
type vlookup then the order ID 839 to 84
then I need to identify the range of the
cells where I will be searching for the
information and the ranges between the
cells of a to to e 23 then I would need
to specify the email column in this
range and the email column is number
three because the first one is order ID
second one is the name and the third one
is the email and then I need to specify
the type of net true is an approximate
match and false is the exact match
so I'm gonna use fools for exact match
if you interested to learn more about
Microsoft Excel
I recommend training course on udemy
just navigate to how to analyze data
that map slash Excel to learn more I've
carefully selected this course and you
get substantial discount from the retail
price just to let you know this isn't a
few a blink and they get small
Commission if you make the order don't
make purchases though if you don't need
it but thank you for your consideration
and support of this channel based on the
information I provided system found
email for Katherine Morfitt let's look
at how vlookup can help us solve
specific business problems for example
how can you find customer name or email
based on specific order number or how
can you find order amount based on order
number or how can you find ship date
first let's look at name and email me
lookup can only return one value so we
would need to do name first and then
email as I started typing please pay
attention that instead of putting
specific order number I put in the cell
reference which is a six in this case
and we'll see how this will impact
copying and pasting of vlookup formula
in solving later business problems we
supply order number by putting the
reference to a six cell then we
specified the range and then we specify
the column ID for where the information
should be taken from and the last step
is we specify how we're gonna match the
data we picked an exact match by
supplying the value false in the name of
Aliyah Reeve has been returned by the
lookup which is the exact correct value
and now let's look at what happens when
you copy and paste build-up formula in
Microsoft Excel as I have already
mentioned we used reference cell instead
of supplying the exact order ID number
so we reference cell a6 in our previous
example now let's see how copying and
pasting of the data in vlookup formula
impacts the values so when I copied and
pasted it I see a different match being
found so let's understand why is this
happening if we look closely into the
formula of the pace that value instead
of pointing to the cell a6 now it points
to cell a10 because we shifted the
formula by three rows so four plus six
plus four would be equal to 10 and
that's the reason it points to a
different cell and as you can see the
new value for the order ID that we're
searching in vlookup is 839 302 if this
is what we want that's fine but in our
case we want to point back and search
for the order ID number eight three nine
two nine zero because this is the
customer email for that order we're
trying to find there are multiple ways
to change and then this is exactly what
we're gonna do next but the important
thing is to understand when we copy and
paste formulas that's what happens with
referential integrity once we change
that we also need to change the column
ID where we'll be finding the value and
now instead of two the ID will be three
because this is the third column that
points to email
now let's go ahead and do it and change
the formula after copy and paste first
let's change the order ID that we are
referencing and as you can see now it
points to the customer name since we
just copied and pasted the formula and
it still points to column 2 instead of
column 3
but now let's change column 2 to column
3 to point to customer email and we see
the correct results returned back by
vlookup formula which is customer email
Aliya Reeve add unto focal com
[Music]
let's continue and see how we can find
or amount based on a specific order
number to do that I'm gonna copy and
paste the formula and modify the fields
so it calculates exactly what we're
looking for once I completed copy and
paste you can see formula cannot
calculate and find the required value
and the reason for that is because range
is shifted and it cannot determine what
is the right well to correct this I'm
going to modify the range and start
selection from the cell a2 as you can
see once I corrected the range it points
to the same value as the previous
formula that we had in cell G 9 which is
email and returns email alia reef at
unto focal comm now my goal is to modify
the input parameter in vlookup formula
so it points to the mount column an
amount column is column number 4 and as
you can see once I change the input
parameter to 4 now points to them out
let's continue into the last step in our
exercises and try to find the ship date
based on the order number to do that I'm
going to copy and paste formula again
I'm going to modify the range and as you
can see once I did this it points to the
amount because this was copy and paste
of the formula and then the last step is
to point to column number five and as
you can see the interesting thing
happens even though we point to column
number five we do not see the date but
we have we see the five digit number but
the reality is this is how Excel stores
the dates using those five digit numbers
so the only thing we can we need to do
is to change the formatting of this
number to do this I'm going to select
the cell and go into the drop-down that
has the formatting and select the
different data type and I'm going to
select a short date and as you can see
once I selected the short date it shows
the correct date for that specific order
number
[Music]
have you enjoyed this episode make sure
to subscribe so you will not miss the
next one and tell your friends about it
you can find links and downloads in the
description section of this video there
are a lot of helpful links and a lot of
other helpful information you will
benefit from make sure to check out my
other relevant videos and subscribe to
my youtube channel we have a lot of
great stuff planned in the pipeline and
I don't want you to miss any of it and
if you'd like to get notified about all
the new stuff that are coming out make
sure to subscribe to my email list as
well all links are here in the screen
make sure to click to stay in touch
thanks again for watching