Everyone in this lecture, we will learn how to apply lookup functions, index and match auctions.
First, you look at function, it stands for vertical, look at this function will search the value
that we specify vertical in a table and then return another value corresponding to that value from the
table.
I know that the definition is not very clear.
So let us look at this example to understand this clearly.
Supposing the small group of five students that we have been studying, you want to add the music schools
for these students, from the music teachers to the music teacher, the student in the list.
Also, the task of matching each student and assigning marks will be very cumbersome if we have a lot
of student.
This is where we look at will help us to use we look up a is equal to we look at selective value, we
want to match the table, which is to be the first.
They mentioned the column number from which we want this response.
This is a column we want to respond to the second column, which contains Monks'.
The fourth parameter is the type of match we want, if we want exact match, there should be zero or
approximate match, they should be one.
We want an exact match for Turin in one value in the table.
So we will use zero percent of.
We see that it went to the table, selected it, matched the look of value vertically to student one
here, it gave back the value in column two, which is fortify, as we have asked to do now, we put
dollar signs on the reference table, values.
And extend the formula to get music math for the students.
Next is executive function, which is to look at in this, the venue will be looked up horizontally
in the table.
You will get the wrong number from which you want to respond to instead of column number.
For example, if you want to add to our table marks of 2006 in maths, science and English from another
table which contains marks of students in all the subjects we will type is equal to each lookup.
Select the subject name.
Then the defense table.
This effort to log the representable values.
Come on, then, give the door number, which is to Nargiz,
zero for exact match.
If of little.
We can do the same for students and also only the rule number will be three instead of two.
Please try that on your own.
Next, we will look at the index function, as the name suggests.
If in any given day will we want to access any particular value index number or the rule and column
numbers are known to us.
We use the index function in this example.
If you want to find the score of a particular student in a particular subject, every first lady student
number or number then translates object name to column number and then using an index function.
This will be the reference table for index function.
You can see that today, no, actually can be used as rule number, so student one, the one student
one is actually the rule one of our table and two on four columns, match marks are in column one since
I didn't column too and English in column three.
So we know down one, two and three over math, science and English.
This conversion we can do by using nested issues as discussed previously.
Now we apply index is equal to index select the table.
Give rule number comma.
Give column number presenter, you can see that first student has scored ninety nine martinets if you
change the number of student.
To do it will be updated at 65, if you change subject to science, it will be updated to 53.
You can try changing different varieties of student and subjects to get different Martez output.
Next up is Matt's function.
Matt's function will return the position of a particular value in an area that is, if you have a list
of numbers, Matt will value the position of a particular number in that list.
So here, if you want to find the student who has scored maximum marks, you need to update.
We first find the maximum marks, coordinates, object using the max function.
Now we will use match function to find the position of this maximum value in the reference list to retape
is equal to match select Essel.
And then the Eddie.
Presenter.
This maximalists report 12 on the list, which also corresponds to for.
Similarly, your play match function horizontally to identify the subject in which each student has
scored the maximum score.
Again, we find the match score using match function.
We then match this match value.
With the table selected horizontally to get the position in position, one represents math and science,
and they represent English student one Matmata Multiregional.
Matt.
Next, we look at how we can use a combination of index and match in case of we look at or look at.
Usually we do not need to do this for small datasets, but if you have a large dataset, we will pick
up a lot of processing power of Excel.
You may have to wait for very long time to get the result.
You may have to look at messages with, say, Excel is not responding, etc..
I like the way to achieve the same objective will be to use index and match combination in this combination
index will return the value desired from the label and match will help us identify the door number or
the column number of the table from which the values to be returned.
For example, if you want music marks of student one, we start typing is equal to.
Index tab, select the reference table.
No, that in the table below the column in which value is to be matched need not be colomban, this
can be an index match.
However, what we look at the reference column should always be the first column.
The second parameter is row number, which will be written by max function.
So we type match select student one.
Into the area below.
Zero.
What exact match bracket?
Comma, column number, which will be one.
And this gives us the same result as we look up, only this is quicker than you look at for dollars
and appropriately and extend to a decent.
That's almost deselected where we covered an important set of local homeless and neglected.
We look at data sorting, filtering and fuel tanks.