Hello and welcome to our new video
In this video we are going to learn about VLOOKUP FORMULA
Now what does VLOOKUP FORMULA do?
It does the work of filtering
So let's see how to apply it and what value is added to it.
So first of all
I open excel
I opened excel and
I already have some data written here.
Some names are written here, their gender is written here,
their age and their interests are written here.
So now how will VLOOKUP FORMULA apply? Now what I want to
know through VLOOKUP FORMULA like here is a row number 7.
So if I enter Hrithik, then I will get to know what is
Hrithik's interest in poetry, music or vlogging.
So as soon as I enter Hrithik
I should get to know his interest.
So this is what VLOOKUP FORMULA does.
It filters things for us.
If I want to know
Deepak's age, here 32.
So I write Deepak and he gives me his age.
So what is he doing?
He is filtering things.
So here we use
VLOOKUP FORMULA.
Select any cell.
So first
I know, now I remember that if I want to use any formula,
then what should I do?
First I have to put an equal sign.
So here I put an equal sign and then I put VLOOKUP.
See, you wrote VL and it automatically told you that you
are looking for VLOOKUP.
We said yes.
So what you have to do is double click and here it is
telling me to put a lookup value.
Okay. Which value do you want to fight the rest of the
values?
I said okay.
Let's put Hrithik here.
So A7 came.
Or leave A7, we don't advance that much.
Let's find it from the name itself.
I will write Hrithik here.
What did I put here?
I put Hrithik here.
After that I put comma.
Now it is asking me to tell the table array.
Now what will come in the table array?
So in the table array, my entire table data will come.
So what I will do is I will select the whole from here to
here.
And from here, my first cell has been selected from there
to the last cell.
From A2 to
D19.
It has been selected.
After this, what I have to do?
After this, it is asking me column index number.
What will I find from Hrithik?
I will find
Hrithik's interest.
So I have to tell
Hrithik's overall interest is written in which column.
Now there is no column number here.
There is no column number here.
So we can simply count.
What happened to A?
Let's assume B.
Let's assume 2. Let's assume C.
Let's assume 3. Let's assume D.
Let's assume 4.
So what can we write here?
We can write 4.
After this, what I have to do?
Now it is asking me range lookup.
Now what I have to put in it?
See, the names we have put are not in alphabetical order.
A to Z, Z to A, ascending and descending order.
So we have to remember that when we have not put the names
in ascending order, in that case, what we will do?
In that case, we will falsify this value.
If I had put it in ascending order, then what I would have
done?
I would have true this value.
So here I have falsified it.
And closed the bracket from here.
And now I will do enter.
So see,
I have found
Hrithik and I have got poetry.
It is simple.
So here if we click on poetry, what happens? See, I have
got formula written here.
Hrithik, A to D, 19, 4 fold.
Now what I have to do?
Instead of Hrithik, if I have to find
Jishan, then I have to go here and do this thing.
I have written Jishan here.
Jishan.
Ok.
And after that I have done enter.
So is Jishan also poetry? Yes, it is poetry.
Ok. Let's do something else.
Let's do Imran.
So I have gone here and changed Imran.
So I have written Jishan here.
Imran. Ok.
And now what I have done?
Here I have done enter.
So the login has come.
But the question is, is this an efficient way? Because
I don't think it is an efficient way.
That I have to go on that formula again and again and then
I have to write again and again that write Jishan or write
Hari Om or write Neeraj.
There is no efficient way.
So what can we do?
What can we do better by going one step?
So we will try to do that.
So what do I do? See, here this formula has come.
I want that here
I have a column here.
I will write something inside this column and what will
come on its side?
Result. Ok.
So let's change here.
Instead of Imran, I will write
G7.
Ok. So here
I will remove this name from here and I will select column
G7.
Ok. So I have written G7 here and I have entered.
Now this is not showing any value.
Now what is the reason not to show value?
Because we have not put any value here yet.
Ok. So what do you have to do here?
What did it mean?
What did it mean to select G7?
It meant that you will put any name here and here what will
be written inside the side cell?
Its interest will be written.
So let's put it here and see. I put Rahul.
Ok. And by putting Rahul, I entered.
Here came the result.
Now it is better than before.
I don't have to go to formula bar and change it again and
again.
I am changing it here.
So instead of Rahul,
I will take some other name.
For example, let's take Sharda.
When I take Sharda, what should come? Music should come.
So here I write Sharda and enter.
See, music has changed.
Now can we call it an efficient way?
Yes, it is better than the previous one.
But it is still not as much better as we want.
What do we want?
Now I have to edit here again and again and change the name.
It should be such that a drop-down list comes.
And there I should get all the names list-wise.
And there
I should select any name and what should come in the side?
Interest.
If such a thing comes, it will be fun.
Let's do it and see.
How to do it?
I have to go to data.
And here you will see an option of data validation.
After going to the option of data validation, what is here?
Click on data validation.
Here it will show me the option allow any value.
In any value, you have to go and select the list.
What did I do here? I selected the list.
And here it is telling me to tell the source.
Let's tell the source.
What do you have to do in the source?
You have to select the entire column.
I selected it.
And what did I do from here?
Why did you select this column?
Because we have to find out from this.
What is the interest?
So what did we do?
We did it from here.
Now you will see that a drop-down list a small icon came.
If I click on it, I have all the names here.
I have to find out Ishaan.
I will click on Ishaan.
Vlogging is written here.
If there is a problem in visibility, we insert a column
from here.
So that it is more clear.
If I change from here, if I change Jiya, then poetry comes.
Apart from this, if I change
Dipanshu, then what comes? Vlogging comes.
Apart from this, if I change
Ritesh, then what comes? Poetry comes.
This is better.
Let's try Vlookup again.
Let's try it with age.
If I try it again,
I write equals to
Vlookup It is written here.
I double-clicked it.
What is the lookup value here?
Let's select it again.
You didn't have to do this.
Right? What did you have to select?
Tell me quickly.
We select
G7 Because we have to find out from here.
So let's write G7 again.
I was cross-checking.
After G7, I have to write the table array.
I will select the entire table.
After this,
I have to put the column index.
What is the column index here? 1, 2, 3
So what did I do?
I put 3.
Let's put the column second.
Let's find out male or female.
But it will be known from the name.
So let's put 3.
After this,
I have to do true or false.
I have to do false.
Because I have not put it in alphabetical order.
So let's do false.
And close it from here.
And enter from here.
So what is written here?
Ritesh poetry 23
Where is Ritesh?
Yes, it is.
Now let's change from here.
So I do Sharda.
So music is here.
And 18 is here.
I am doing Hariram, so the music has come, 27 has come, it
is not that you have written it in a row or horizontally,
so you can write it like this, you can do it here too, if
you do it here too, then there is no problem, like I cut it
from here and paste it here, or wherever, like I paste it
here too, even Ctrl V, then it will show, now let's change
it and see it again.
Tipanshu Vlogging 24 is clear, so we look up, we have
understood well that what does it do, how does it work, I
think you will not have any doubt in it, if there is still
any doubt, then you have to play this video again and see,
I will meet you in the next video, till then keep
practicing.