Hello and welcome to our new video
In this video we are going to see some examples of VLOOKUP
formula
In last video we have understood a little bit about VLOOKUP
formula and practiced a little bit but in this video we are
going to try to understand it through one or two examples
so that our concept becomes clear so first of all I will
open excel and here I have some data, employee
ID, name, age and this is city column but in city column
there is no city so I will take it to next sheet what is
here, here employees name is written and city is written
now what I want to do is use formula of VLOOKUP and sheet
no.3 here in city nothing is written from sheet no.4
I want to see data on sheet no.3 means zareen is written
here zareen is from Mumbai
I want zareen is in last and Mumbai is here we use formula
of VLOOKUP and Mumbai is written here now here one thing
you have to notice see order of name first of all Rahul is
written here Imran is written if I go to sheet no.4 see here
Rahul is written zareen is written so we will see this with
the help of formula of VLOOKUP now this is not order wise
in order of sheet no.3 in order of sheet no.4 city is
written nothing like this still what will happen when we
will apply formula of VLOOKUP then it will automatically
correct so lets see how to apply formula of VLOOKUP so lets
try again so I go to city
I want to know city of Rahul so what is written in sheet
no.4 it should be consolidated so what we have to do for
formula of VLOOKUP first of all I have to put sign of
equals to we know this after that I have to write
VLOOKUP it suggests
I double click from here it is asking lookup value we have
to tell value so what we have to write in lookup value what
we have written in last do you remember we have to give
name here for which we have to find value like if I give
Rahul then I have to find city of Rahul so what I have to
give
Rahul so I have given Rahul
Rahul in which cell
I will write name of cell
B2 after that I put comma and it is asking table array from
which table you want to give so we know this is sheet no.4
this is whole table from here to here this whole table
I have selected you can see formula written here sheet no.4
from A2 to B14 after this what I have to do
I go back sheet no.3 ok it is written here
I put comma and it is asking column index
I have to tell which column data I want so let's go to
sheet no.4 which column is this first column is this second
column is this and what we want name of city so this is
column no.2 so here in sheet no.4 sorry in sheet no.3 here
in sheet no.3
I put here in sheet no.3 it is wrong because we don't want
to put sheet no.3 we want to put column index and what is
index of column it is 2 and after that what we have to put
range lookup in this what we have to put true or false in
programming if you don't know programming is true or false
so what I do
I turn on the fan when I turn it on it means it is true if
you put 1 instead of true it is true if you put 0 instead
of false it is true here it is written true or false we
know why it is true or false we have seen in last video if
in disordered things are not given in that case
I select false if in ordered things
I select true here if I want to select false what I wrote
in last video
I wrote false but here
I can write 0 instead of false if I write 0 and I close it
and enter it shows nothing it shows error we have to check
let's go to sheet no.4 and here Rahul city should be
Faridabad but it is not did we make any mistake in formula
B2 here it is sheet no.3 here it is sheet no.4 we wrote
wrong we have to write sheet no.4 not sheet no.3
I did sheet no.4 and then A2 and B14 ok is it in sheet no.4
from A2 to B14 let's see from A2 to here let's see
B14 ok from A2 to B14 now it is
Faridabad now I know
Faridabad does Rahul live in Faridabad yes in sheet no.3
now we have to drag we know this so we drag it from here
but in last it is not showing any value
Ishani and Zareen why it is not showing
Ishani and Zareen and Zareen now what is the reason let me
tell you we did not lock the value like we selected cell in
sheet no.4 from A13 to
B25 we selected this thing there but we did not lock it let
me tell you how to lock it it is not showing any value so
what we have to do from here from sheet no.4 to B25 we have
to select it and press F4 if you press F4 what will come
dollar dollar dollar dollar let me show you let me remove
it once delete it leave Faridabad leave Faridabad and
delete other data
I deleted it and when I go here let me select it again and
after that press F4 and enter this is Faridabad now if I
drag it does it show invalid data or it shows correct data
now it shows correct data so we got to know that the range
of cell we are selecting, we have to lock it otherwise it
shows invalid data so we got to know this much in this
example what we have to do we have to check one more thing
in sheet no.3 it was unordered but in sheet no.3 it was
Rahul so now what is the wrong data
Zareen's data is in Mumbai
Imran's data is in Itawa now let's cross check
Zareen's data is in Mumbai or Imran's data is in Itawa if
we go to Zareen's data
Zareen's data is in Mumbai and Imran's data is in Itawa so
it doesn't matter whether it was ordered or unordered it
automatically put things in correct place the city in front
of the name so I think this example will be clear how we
have to use
VLOOKUP formula after this what we have to see one more
thing till now we were using single formula it must be
single it can't be your question but I will say it is not
single we can use double VLOOKUP and this question is asked
so many times in interview you can ask vlookup and hlookup
other than this ask can we do double vlookup in vlookup
answer is yes let me show you how to do double vlookup what
we need do
I have given data on different sheet like sheet no.2 sheet
no.1 let's increase increase it.
I will delete this data from here first because what we
have to do is we have to do the same thing that we have
written this data from sheet no.
2. Now what is this data? These are the names of the states
and what is written here? The name of the state chief
minister is written here.
So we simply have to do this, the name of the chief
minister is written here.
You will say that it is the same example.
So let's do this example and I will tell you a few more
things.
One way is to use the formula of VLOOKUP.
Other than this, what can we do? Other than this, what can
be the steps? What are the ways by which we can do the same
thing very easily? I will tell you that too.
So first of all what you have to do is that in my sheet no.
2, the names of the chief ministers are written and this is
unordered data.
If I compare it with sheet no.
1, what is written in sheet no.
1? Goa is written, Bihar, Gujarat is written.
Is Goa, Bihar and Gujarat written here?
No, Manipur is written here, Gujarat is written and after
that Andhra Pradesh is written.
So first of all, what do we do? Let's go to sheet no.
1 and after going to sheet no.
1, first of all, I will put the sign of equals to and then
I will write VLOOKUP.
Will it show me? It will definitely show me.
I clicked VLOOKUP, double click and then it is saying that
look up value.
What do you have to find? Okay, what do you have to find?
For example, what do I have to do? I have to find that if I
write Goa, what should I get? I have to
get the name of the chief minister of Goa.
So what will I write in lookup value? Like we used to do in
the name, what were we doing before? We were finding out
things from the name that this is the name of Rahul and
bring me his city.
This is what we were doing and this is the same thing we
were doing in the video we saw before.
So what will we do here too? What I want is that I write
Goa and its chief minister's name comes.
So I select
Goa from here.
After that, I put coma.
After that, what is the table? Now the chief minister of
Goa is fine.
Which table is he in? So I will have to select the whole
table.
Now we know that it is not in this sheet.
In which sheet? In sheet no.
2. So where do I have to go? I will have to go to sheet no.
2. So I go to sheet no.
2 and from here I select the whole thing.
So you will see one thing here in sheet no.
2 that the list sheet is coming here.
I have already done this, so it is showing you the list
sheet.
I delete it from here so that we can see it again.
What do you do from here? Remove it once.
Just give me two minutes.
If I go to the first sheet, then here you see the list sheet
is coming.
I will have to remove it from here.
So I go to sheet no.
2 and from here I select
the whole thing.
So you will see one thing here in sheet no.
2 that the list sheet is
coming. I will have to remove it from here so that we can
see it again.
So I go to sheet
no. 2 and from here I select the whole thing. So you will
see one thing here in sheet no.
2 that the list sheet is coming.
I will have to remove it once.
So you will see one thing
here in sheet no.
2 that the list sheet is coming.
I will have to remove it once.
So you
will see one thing here in sheet no.
2 that the list sheet is coming.
I will have to
remove it once.
So you will see one thing here in sheet no.
2 that the list sheet is
coming. I will have to remove it once.
So you will see one thing here in sheet no.
that the list sheet is coming.
I will have to remove it once.
So you will see one thing
here in sheet no.
2 that the list sheet is coming.
I will have to remove it once.
So you
will see one thing here in sheet no.
2 that the list sheet is coming.
I will have to
remove it once.
So you will see one thing here in sheet no.
2 that the list sheet is
coming. I will have to remove it once.
So you will see one thing here in sheet no.
2 that
the list sheet is coming.
I will have to remove it once.
So you will see one thing here in
sheet no.
2 that the list sheet is coming.
I will have to remove it once.
So you will
see one thing here in sheet no.
2 that the list sheet is coming.
I will have to remove
it once. So you will see one thing here in sheet no. 2 that
the list sheet is coming.
I will have to remove it once.
So you will see one thing here in sheet no.
2 that the is it correct data like here
Kerala Chief Minister
Pinarayi Vijayan
I am pronouncing wrong
Maharashtra's Uddhav Thackeray
Odisha's Naveen Patnaik let's see if Maharashtra and Odisha
are same means here also data is same
Odisha's Naveen Patnaik
Odisha's Naveen Patnaik is correct
Bihar's Nitish Kumar is correct means data is correct
now what I have to do
I have to do one thing
this much is clear now let's see how to use double
VLOOKUP what is the meaning of double VLOOKUP we saw
formula of one VLOOKUP name but is it possible from
employee's ID we can find city's name answer is yes how
will they do let's see for example
I take employee's ID 245 when I take 245 means Delhi should
come how will it come but from employee's ID let's find
city's name after that we will use double VLOOKUP to find
city's name from employee's ID
I have to do equals to VLOOKUP
I selected lookup value
I told we want from this value we want from 245 we can find
city's name after that second thing table's array
I selected
I want this much now what I am doing from this ID
I am trying to find city's name after that what I will do
I have to put column number what I have to find
I told we want to find city's name for that put 2 comma
after that put 0 for false press enter now 245 is
Jishan's now I have to find city's name now what we have to
do we have to put one more VLOOKUP
I will delete it because I want to know the city and I want
to know from this sheet so
I will delete it here because it is of no use here and now
let's go double click here so what you have to do in double
VLOOKUP you have to put one more VLOOKUP here you have to
put it here now what is it doing? the value from here to
here what does it consider it? like what was the first
value in VLOOKUP? what do you want to search from which
thing? what do you want to search from ID? do you want to
search name? this is what used to happen so what do we do?
what does it mean? what did we get
this formula? that we were searching name from ID but what
do we want? that we get city from ID so what we will do?
that our first argument will be our VLOOKUP value like what
did we write before? see what was written here? we wrote E4
here but now what will we write here? in VLOOKUP value we
will write this whole after this what to do? after this we
have to put comma second thing is that you have to tell
which is table array? so table array I know that city is
written from this sheet so what I
to do for table array?
I have to select this whole
I have selected it from here you can see above that what is
written sheet is selected after that I have to put comma
this is also known so here which is column index? we know
that because I have to find city from ID so what will be
the number of column? it will be 2 so what we have to do?
we have to put 0 why we have to put 0? we know that it is
for false but if things are unordered like here things are
unordered
I have to show in other sheet that Mumbai is written in
front of Zareen so I put 0
I closed it from here and entered so what is written here?
here if I change any value it should be changed so what we
were doing before? before we put VLOOKUP we took name from
ID but now we are doing we are finding city name from ID so
I took Delhi from 245 if I change it so what?
Delhi will also change so we put 564 we put Hari Om's ID so
what is this? it is Jaipur so now I know how to do it now I
will tell you how to do it so what I have to do?
I have to go here formula tab in formula tab go to name
manager go to name manager now what I have to do on new? I
have to write list sheet do you remember? so from here
I have to put underscore because if I put minus it will not
take and it does not take any sign you can put underscore
because it connects two names but cannot connect minus so
what I have to do? list sheet and here I have to select
currently I have selected sheet 3 and here I have to select
I have to select sheet 4
I have to select so I select from here to here so it is
selected so this data I have to put range
I have to put table array instead of table array
I have to put list sheet so I have to do ok and it is added
from here
I have to close so now what I have to do? let's see again
let's see again what is here? this is sheet 4
I have to remove it after removing what I have to do?
here I have to write list sheet list sheet and from here I
have to enter ok if I change it 867 ok ok you can do this
I go back what I did here?
I gave a name to table array simple ok from where I gave
name?
I went to formula tab
I went to name manager you can see from name name manager
let's close it let's see another way
I remove it and here was city in this way you have to
remember employee id should not be written ok you will find
city from employee name ok but if there is any column in
front of it in which number will be written it will give
wrong data so what we do? we delete this column what we
want? in sheet 4 we can remove data easily from VLOOKUP but
I am going to tell you another way which may be easy we
have to go to lookup and references and after this we have
to go to VLOOKUP after going to VLOOKUP,
is lookup value? from whom you want to find lookup value?
I said from Rahul from Rahul
Rahul lives in which city? so we put A2 after that table
array list sheet list underscore sheet what is this? here
is full data
Zareen lives in Mumbai
Imran lives in Itawa sheet 4 data column index number
second number after that palwal after that zero after that
ordered after that unordered Faridabad after that drag drag
drag drag drag double VLOOKUP double VLOOKUP double VLOOKUP
== == == == == == == == == == == == == == So, I hope you
understood this video.
If you have any doubt then you can play it again.
I will meet you in next video.
Till then keep practicing.