Hello and welcome to our new video.
In this video we are going to learn
What we saw in last video
Index formula
In that we will do
Match function
Now what will be the benefit of this
Benefit will be
We will get an alternate of VLOOKUP
If I use
Match function with
Index formula
Then I get alternate of VLOOKUP
So this is what we are going to see
Data will be same as we saw in last video
So first of all I will open
Excel I have same data 20
Employees ID
Name, Age, Address and Salary
What I have to do
I have to do this
I have to use match function
I will tell you how to use match function
First of all I will tell you
What does match function do
If I change my name
Then it will give me
Role number
If I change my name
Then it will give me
Role number
Now First of all
Let's make a drop down list
So that it becomes easy for us
If we change anything
Like I change
Jishan, Deepak
Hrithik Whatever particular
I want to find
Like I want to find address
Then it will come in front of me
So for this we saw earlier
We used to make drop down list
With the help of data validation
First of all I will choose
Like I have selected Hrithik
And I have entered
Now see this single name is coming
Now I have to change single name in drop down list
So I know something like data validation
So what you have to do
Here is data validation
If you are on home
Then you can come to data tab
And after coming to data tab
Here you will see data validation
You have to go to data validation
And then it asks you
Allow In this you have to tell
What do you want in list form
Or there are other options
Like you want date wise, decimal wise
Whole number wise
So you will get to see a lot of things
After that what you have to do
You have to put in source
This whole column
Because this is what we are going to do
If I change any other name
So according to that
The data that is lying with me
Like in address
I want to see Agra, Mumbai
This is what we want to do
So what I will do here
Select this whole
And then what you have to do
You have to press ok
Make sign
You will click this
You will change
So now because
We have not put anything
Neither we have put index nor match
So where will the data come from
So we are going to do this
In this cell
We are going to put index formula
And we want that when we change the name
So the data will change here too
So how will you put index
You will put equals
Double click
Here first ask
Tell me the array
I will tell you the array
So here we have selected it
After that I have to put comma here
And here I have to write row number
Now the number of row
What we have told
What does the match function do
It fetches the number of the row and returns it to us
So instead of putting the number of the row
What will we put
We will use the match function
Double click
Tell me the lookup value
What is the lookup value
Whose data do you want to find
I said Harish
So I selected it from here
After that ask
Tell me the lookup array
So I said the name you see
You find me from this
After that ask
Match type
What will you write
And I closed the bracket
Enter Found a typo
Do you want to accept this correction
Oh my god
What is this showing
From A2 to F21
Match G11
After that from C2 to C1
And After that
I will type this formula again
Because there should be no mistake
Equals I wrote index
And here
Ask me
So I selected the array
From here
I put it in the row number
Match Now I know where the mistake was
We did not put the column number
Match Lookup value
Selected After that
I put the lookup array
Select this
And After that
I wrote the exact match
I closed the bracket
Now see the match function
Bracket is closed
After that
You have to write the column number
Whose data do you want
I said I want
Tell me the salary
I want to know the salary of Harish
What will I do here
On which number
I wrote 6
And closed the bracket
And it came
Now see the salary of Harish
Harish here
Now from here
If you change
Like Rajesh
Or Rani Or Mukesh Now what we see
We try to find ID
ID I want to know
I want to do the same thing
I put it here
Equals to
Index Double click
Here the array is asking
What do you put in the array
I put the table
Selected After that
We do not have to put the row number
We have to put the match function
What will we write here
Match Lookup value
We put Mukesh
Comma Lookup array
From which we are looking up
From name
Selected Comma again
Exact match
Double click
You can also write 0
Bracket is closed
We have to put the column number
What is the column number here
The first column is this
We put 2
And I entered it
What is this
The ID here
Whose ID is this 45 We have to see from here
We put Mukesh
Mukesh Mukesh Mukesh Mukesh Mukesh From here we change it
We just enter Shrey
Shrey ID
Salary Salary ml This is working I hope
You must have understood if you have any doubt in this video
I will meet you in next video till then keep practicing.