1
00:00:06,900 --> 00:00:12,780
Everyone in this lecture, we will learn how to apply lookup functions, index and match auctions.

2
00:00:13,850 --> 00:00:19,910
First, you look at function, it stands for vertical, look at this function will search the value

3
00:00:19,910 --> 00:00:25,820
that we specify vertical in a table and then return another value corresponding to that value from the

4
00:00:25,820 --> 00:00:26,120
table.

5
00:00:26,660 --> 00:00:28,750
I know that the definition is not very clear.

6
00:00:28,760 --> 00:00:31,910
So let us look at this example to understand this clearly.

7
00:00:33,390 --> 00:00:39,180
Supposing the small group of five students that we have been studying, you want to add the music schools

8
00:00:39,180 --> 00:00:44,250
for these students, from the music teachers to the music teacher, the student in the list.

9
00:00:44,250 --> 00:00:50,070
Also, the task of matching each student and assigning marks will be very cumbersome if we have a lot

10
00:00:50,070 --> 00:00:50,580
of student.

11
00:00:52,050 --> 00:01:00,330
This is where we look at will help us to use we look up a is equal to we look at selective value, we

12
00:01:00,330 --> 00:01:05,310
want to match the table, which is to be the first.

13
00:01:08,780 --> 00:01:11,810
They mentioned the column number from which we want this response.

14
00:01:12,890 --> 00:01:16,670
This is a column we want to respond to the second column, which contains Monks'.

15
00:01:17,680 --> 00:01:23,800
The fourth parameter is the type of match we want, if we want exact match, there should be zero or

16
00:01:23,800 --> 00:01:25,390
approximate match, they should be one.

17
00:01:25,400 --> 00:01:28,900
We want an exact match for Turin in one value in the table.

18
00:01:28,970 --> 00:01:31,300
So we will use zero percent of.

19
00:01:32,790 --> 00:01:39,390
We see that it went to the table, selected it, matched the look of value vertically to student one

20
00:01:39,390 --> 00:01:46,170
here, it gave back the value in column two, which is fortify, as we have asked to do now, we put

21
00:01:46,170 --> 00:01:48,360
dollar signs on the reference table, values.

22
00:01:50,010 --> 00:01:54,210
And extend the formula to get music math for the students.

23
00:02:01,720 --> 00:02:07,840
Next is executive function, which is to look at in this, the venue will be looked up horizontally

24
00:02:07,840 --> 00:02:08,370
in the table.

25
00:02:08,830 --> 00:02:12,940
You will get the wrong number from which you want to respond to instead of column number.

26
00:02:13,690 --> 00:02:20,200
For example, if you want to add to our table marks of 2006 in maths, science and English from another

27
00:02:20,200 --> 00:02:25,180
table which contains marks of students in all the subjects we will type is equal to each lookup.

28
00:02:27,950 --> 00:02:29,580
Select the subject name.

29
00:02:31,690 --> 00:02:33,290
Then the defense table.

30
00:02:37,010 --> 00:02:40,790
This effort to log the representable values.

31
00:02:45,060 --> 00:02:48,720
Come on, then, give the door number, which is to Nargiz,

32
00:02:52,110 --> 00:02:53,460
zero for exact match.

33
00:02:55,420 --> 00:02:56,580
If of little.

34
00:03:01,980 --> 00:03:05,980
We can do the same for students and also only the rule number will be three instead of two.

35
00:03:06,240 --> 00:03:07,440
Please try that on your own.

36
00:03:08,110 --> 00:03:11,220
Next, we will look at the index function, as the name suggests.

37
00:03:11,220 --> 00:03:17,550
If in any given day will we want to access any particular value index number or the rule and column

38
00:03:17,550 --> 00:03:18,740
numbers are known to us.

39
00:03:18,990 --> 00:03:21,740
We use the index function in this example.

40
00:03:22,170 --> 00:03:27,720
If you want to find the score of a particular student in a particular subject, every first lady student

41
00:03:27,720 --> 00:03:34,620
number or number then translates object name to column number and then using an index function.

42
00:03:35,750 --> 00:03:39,050
This will be the reference table for index function.

43
00:03:41,800 --> 00:03:48,550
You can see that today, no, actually can be used as rule number, so student one, the one student

44
00:03:48,550 --> 00:03:56,770
one is actually the rule one of our table and two on four columns, match marks are in column one since

45
00:03:56,770 --> 00:03:59,470
I didn't column too and English in column three.

46
00:03:59,800 --> 00:04:04,780
So we know down one, two and three over math, science and English.

47
00:04:08,920 --> 00:04:14,650
This conversion we can do by using nested issues as discussed previously.

48
00:04:15,580 --> 00:04:21,280
Now we apply index is equal to index select the table.

49
00:04:23,870 --> 00:04:26,330
Give rule number comma.

50
00:04:29,640 --> 00:04:38,700
Give column number presenter, you can see that first student has scored ninety nine martinets if you

51
00:04:38,700 --> 00:04:40,020
change the number of student.

52
00:04:41,350 --> 00:04:50,380
To do it will be updated at 65, if you change subject to science, it will be updated to 53.

53
00:04:51,400 --> 00:04:56,140
You can try changing different varieties of student and subjects to get different Martez output.

54
00:04:56,620 --> 00:04:58,390
Next up is Matt's function.

55
00:04:59,140 --> 00:05:04,660
Matt's function will return the position of a particular value in an area that is, if you have a list

56
00:05:04,660 --> 00:05:08,800
of numbers, Matt will value the position of a particular number in that list.

57
00:05:09,400 --> 00:05:15,550
So here, if you want to find the student who has scored maximum marks, you need to update.

58
00:05:16,060 --> 00:05:19,900
We first find the maximum marks, coordinates, object using the max function.

59
00:05:21,240 --> 00:05:28,170
Now we will use match function to find the position of this maximum value in the reference list to retape

60
00:05:28,170 --> 00:05:32,700
is equal to match select Essel.

61
00:05:36,470 --> 00:05:37,640
And then the Eddie.

62
00:05:39,920 --> 00:05:40,560
Presenter.

63
00:05:44,840 --> 00:05:49,520
This maximalists report 12 on the list, which also corresponds to for.

64
00:05:56,570 --> 00:06:02,930
Similarly, your play match function horizontally to identify the subject in which each student has

65
00:06:02,930 --> 00:06:04,100
scored the maximum score.

66
00:06:05,580 --> 00:06:08,790
Again, we find the match score using match function.

67
00:06:10,220 --> 00:06:11,990
We then match this match value.

68
00:06:12,920 --> 00:06:20,300
With the table selected horizontally to get the position in position, one represents math and science,

69
00:06:20,300 --> 00:06:24,980
and they represent English student one Matmata Multiregional.

70
00:06:26,090 --> 00:06:26,600
Matt.

71
00:06:28,950 --> 00:06:34,470
Next, we look at how we can use a combination of index and match in case of we look at or look at.

72
00:06:36,150 --> 00:06:42,690
Usually we do not need to do this for small datasets, but if you have a large dataset, we will pick

73
00:06:42,690 --> 00:06:44,460
up a lot of processing power of Excel.

74
00:06:44,610 --> 00:06:47,240
You may have to wait for very long time to get the result.

75
00:06:47,760 --> 00:06:51,350
You may have to look at messages with, say, Excel is not responding, etc..

76
00:06:52,380 --> 00:06:58,890
I like the way to achieve the same objective will be to use index and match combination in this combination

77
00:06:58,890 --> 00:07:04,330
index will return the value desired from the label and match will help us identify the door number or

78
00:07:04,330 --> 00:07:07,560
the column number of the table from which the values to be returned.

79
00:07:09,190 --> 00:07:16,210
For example, if you want music marks of student one, we start typing is equal to.

80
00:07:19,050 --> 00:07:23,700
Index tab, select the reference table.

81
00:07:26,770 --> 00:07:32,920
No, that in the table below the column in which value is to be matched need not be colomban, this

82
00:07:32,920 --> 00:07:34,720
can be an index match.

83
00:07:34,880 --> 00:07:39,260
However, what we look at the reference column should always be the first column.

84
00:07:39,910 --> 00:07:43,850
The second parameter is row number, which will be written by max function.

85
00:07:43,870 --> 00:07:48,190
So we type match select student one.

86
00:07:52,180 --> 00:07:53,530
Into the area below.

87
00:07:57,250 --> 00:07:57,670
Zero.

88
00:07:58,890 --> 00:08:01,630
What exact match bracket?

89
00:08:02,830 --> 00:08:06,880
Comma, column number, which will be one.

90
00:08:09,650 --> 00:08:18,090
And this gives us the same result as we look up, only this is quicker than you look at for dollars

91
00:08:18,110 --> 00:08:20,990
and appropriately and extend to a decent.

92
00:08:35,570 --> 00:08:40,870
That's almost deselected where we covered an important set of local homeless and neglected.

93
00:08:40,960 --> 00:08:46,280
We look at data sorting, filtering and fuel tanks.