Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:04,280 --> 00:00:11,950 In this lecture I'm going to show you how you can use Index and Match to solve complex LOOKUP problems. 2 00:00:11,950 --> 00:00:19,840 The thing with Index and Match is that it's not a VLookUp. It's much better than a VLookUp and 3 00:00:19,840 --> 00:00:25,210 you are going to come across situations or you have probably come already across situations where VLookUp 4 00:00:25,210 --> 00:00:27,770 just wasn't working. 5 00:00:27,790 --> 00:00:33,960 It couldn't do the Look-Up that you wanted because your Look-Up problem was too complex. 6 00:00:33,970 --> 00:00:38,510 So that's exactly when Index and Match can come to the rescue. 7 00:00:39,270 --> 00:00:47,940 It was difficult for me to start using Index and Match. Just like a habit I had to force myself at the 8 00:00:47,940 --> 00:00:52,250 beginning to use it until I got the hang of it. 9 00:00:53,090 --> 00:01:01,220 Now, what I'm going to do in this lecture is first to explain to you how Index works in easy terms and 10 00:01:01,220 --> 00:01:05,210 then I'm going to show you how Match works and then we are going to put these together. 11 00:01:05,240 --> 00:01:14,990 So the example I have is a list of divisions, apps, revenue, and profit. The aim of our formula is that 12 00:01:14,990 --> 00:01:21,590 we want someone to select an app here, so let's say "Misty Wash" and we want to get the division first. 13 00:01:21,920 --> 00:01:27,340 So you can see by the order of these, apps is here, division is here. 14 00:01:27,500 --> 00:01:33,260 Would VLookUP work? The classical VLookup is not going to work because you will need to have 15 00:01:33,620 --> 00:01:36,890 apps on this side and division on this side. 16 00:01:36,980 --> 00:01:38,720 So, that's why Index and Match 17 00:01:38,750 --> 00:01:40,610 is great for this. 18 00:01:40,610 --> 00:01:44,980 So let me show you what Index does on its own alone. 19 00:01:45,110 --> 00:01:49,880 The first argument in Index is the array argument. 20 00:01:49,880 --> 00:02:00,020 Think of it like this: Index is like a GPS and for this GPS you need to upload a map on there. Your 21 00:02:00,020 --> 00:02:03,060 map is your array. 22 00:02:03,300 --> 00:02:09,330 OK, if I highlight this, that's my map. And what map do you give it? 23 00:02:09,580 --> 00:02:15,220 Well, the only map it needs is the map that has your answer in it. 24 00:02:15,220 --> 00:02:18,940 It doesn't matter what you're lookup problem is. 25 00:02:18,940 --> 00:02:25,200 It doesn't matter in this case that we're looking for an app and it's called "Misty Wash". 26 00:02:25,390 --> 00:02:28,740 I don't need to include that in my map. 27 00:02:28,870 --> 00:02:33,030 I only need to include in the map where my answer is. 28 00:02:33,190 --> 00:02:38,940 If my answer was also going to be here or here or here, I have to extend my map. 29 00:02:39,040 --> 00:02:44,020 But in this case I know that I want a division and the division is somewhere here. 30 00:02:44,050 --> 00:02:53,160 That's all I need to include. The next argument is basically how many rows do you need to 31 00:02:53,160 --> 00:02:56,070 go down and how many columns you need to move across. 32 00:02:56,070 --> 00:03:04,010 Think of it like the longtitude and latitude in a map. And these arguments are numbers that you give it. 33 00:03:04,010 --> 00:03:11,210 Let's say if I say move down two rows, I close the bracket because the last argument you see it's 34 00:03:11,210 --> 00:03:12,380 in square brackets. 35 00:03:12,380 --> 00:03:13,710 It means it's optional. 36 00:03:13,730 --> 00:03:15,080 It's not necessary 37 00:03:15,140 --> 00:03:21,080 and in this case anyway I just have one column, so I'm going to put 2. I get "Game" 38 00:03:21,140 --> 00:03:21,770 Why? 39 00:03:21,830 --> 00:03:25,960 Well, I index what? This area, right? 40 00:03:26,210 --> 00:03:28,020 And it counts like this. 41 00:03:28,040 --> 00:03:29,070 This is a 1 42 00:03:29,090 --> 00:03:30,180 This is a 2. 43 00:03:30,410 --> 00:03:34,350 So it returned the second place and that's division. 44 00:03:34,730 --> 00:03:37,020 Well, what happens now 45 00:03:38,230 --> 00:03:45,140 if I put one in there and I close the bracket? It's still "Game" 46 00:03:45,160 --> 00:03:46,750 It's one column. 47 00:03:46,780 --> 00:03:48,080 If I put a 0. 48 00:03:48,130 --> 00:03:48,760 What happens? 49 00:03:48,760 --> 00:03:49,880 It's still "Game" 50 00:03:49,900 --> 00:03:58,330 Excel realizes that it's one column but what happens if I put a two here? "#REF!". I'm moving outside 51 00:03:58,390 --> 00:03:59,920 my map. 52 00:03:59,930 --> 00:04:06,260 If I was going to do that, if I really think that my answer is actually somewhere here. 53 00:04:06,490 --> 00:04:09,680 All I have to do is extend my map. 54 00:04:09,820 --> 00:04:20,540 Instead of A6 to A15, I'm going to look until B15 and then it works. OK, so that's all there is 55 00:04:20,630 --> 00:04:22,180 with index. 56 00:04:22,200 --> 00:04:26,250 Now, the part that we want to automate. 57 00:04:26,250 --> 00:04:31,320 Now obviously we're not going to put 2 and 1 and the numbers in. The part that we want to automate 58 00:04:31,410 --> 00:04:32,600 is the two. 59 00:04:32,880 --> 00:04:33,280 OK. 60 00:04:33,300 --> 00:04:36,840 Is this row number argument. 61 00:04:36,890 --> 00:04:44,900 This is where you need a function that is going to return a number to the Index. Which functions return 62 00:04:44,900 --> 00:04:45,980 numbers? 63 00:04:46,010 --> 00:04:47,260 Let's think of a few. 64 00:04:47,450 --> 00:04:49,990 You have the Count function, right? 65 00:04:50,000 --> 00:04:51,410 You have CountA. 66 00:04:51,470 --> 00:04:54,170 You have the Row, you have the Column function. 67 00:04:54,230 --> 00:05:01,430 Sometimes you could use these as arguments in the index function but in most cases the function that 68 00:05:01,700 --> 00:05:07,270 works in harmony with Index, that you're going to need is the Match function. 69 00:05:07,580 --> 00:05:16,490 Let's just write here and see what Match does on its own. Match needs a lookup value. 70 00:05:16,560 --> 00:05:18,610 What is it looking for? 71 00:05:18,610 --> 00:05:25,510 In this case we are looking for "Misty Wash" and it needs the lookup array. 72 00:05:25,510 --> 00:05:28,980 So where should it find this? 73 00:05:29,030 --> 00:05:31,160 In this case it's here. 74 00:05:31,220 --> 00:05:36,960 One thing you need to watch out with the Match function is that it needs a one way street. 75 00:05:37,010 --> 00:05:43,190 You cannot give it something like this because then it doesn't know, should it look this way or should 76 00:05:43,190 --> 00:05:44,300 it look this way. 77 00:05:44,480 --> 00:05:47,710 So it has to be a one way street. 78 00:05:47,810 --> 00:05:51,210 So let's go back. 79 00:05:51,330 --> 00:05:54,390 OK so that's where we should find it. 80 00:05:54,390 --> 00:05:59,110 And then the last argument is the Match type, do you want an exact Match, less than or greater than. 81 00:05:59,490 --> 00:06:06,120 In most cases you're going to need an exact Match, so that's like the false argument in VlookUp. 82 00:06:06,120 --> 00:06:11,700 If your data was sorted and you're looking for an approximate Match then you're going to need less than 83 00:06:11,730 --> 00:06:15,370 or greater than but majority of the cases it's going to be zero. 84 00:06:15,600 --> 00:06:18,380 So what am I going to get? 9. 85 00:06:18,420 --> 00:06:19,540 What does that mean? 86 00:06:20,250 --> 00:06:25,410 That means that Misty wash is the ninth position in here. 87 00:06:26,330 --> 00:06:27,810 Is it the ninth position? 88 00:06:27,950 --> 00:06:29,230 Yes it is, right? 89 00:06:29,880 --> 00:06:35,500 That's exactly the argument that we are going to return to the Index function. 90 00:06:35,530 --> 00:06:39,410 Let's type this now, this full formula. 91 00:06:39,640 --> 00:06:44,660 First what comes in the index argument? Where we think the answer is. 92 00:06:44,660 --> 00:06:48,810 The map that contains the answer and that's that. 93 00:06:49,010 --> 00:06:50,430 What is our row argument? 94 00:06:50,450 --> 00:06:56,810 Well, we are going to use Match to figure it out for the Index argument and we are going to Match this 95 00:06:56,810 --> 00:06:58,220 one. 96 00:06:58,220 --> 00:07:04,590 Where in here and we're going to look for an exact Match. 97 00:07:05,600 --> 00:07:07,920 Bracket close two times. 98 00:07:07,970 --> 00:07:17,750 The only important thing here is that I have the same length. The same array length for both my Index and 99 00:07:17,750 --> 00:07:20,590 the Match functions because they need to be in sync. 100 00:07:20,780 --> 00:07:22,050 Right? 101 00:07:22,110 --> 00:07:29,870 And this gives me "Utility" because if they're not, I'm going to be returning the wrong address to the 102 00:07:29,960 --> 00:07:31,340 Index function. 103 00:07:31,340 --> 00:07:35,520 Now we're going to do the same thing for profit. 104 00:07:35,620 --> 00:07:42,210 We're going to Index. What should we Index right now? This column. 105 00:07:42,220 --> 00:07:43,930 That's all I need. 106 00:07:44,740 --> 00:07:47,470 And how many rows should I move down? 107 00:07:49,080 --> 00:07:51,060 I'm going to use the Match function. 108 00:07:51,060 --> 00:07:52,450 What am I looking up? 109 00:07:52,530 --> 00:07:56,090 I'm looking up Misty Wash. Where am I looking it up? 110 00:07:56,190 --> 00:07:58,310 Only in here. 111 00:07:58,470 --> 00:08:05,690 Arrays have the same height and I'm looking for an exact Match. Bracket close, close. 112 00:08:06,020 --> 00:08:08,190 And that's my number. 113 00:08:08,390 --> 00:08:10,990 That's a simple Index and Match 114 00:08:11,060 --> 00:08:16,950 but what if I wanted to switch between profits and revenue here. 115 00:08:17,150 --> 00:08:18,710 So let's do something. 116 00:08:18,770 --> 00:08:23,450 Let's add a validation to this. 117 00:08:23,470 --> 00:08:34,110 I'm going to put data validation, list and I want these two. Here, what I want to do is to be able to 118 00:08:34,110 --> 00:08:39,130 switch between revenue and profit and this number should obviously change. 119 00:08:39,330 --> 00:08:40,540 How do I do that? 120 00:08:43,340 --> 00:08:46,590 That's when I need to use the Column argument. 121 00:08:46,610 --> 00:08:47,640 Right? 122 00:08:47,990 --> 00:08:57,010 But is that the only thing I need to add or do I need to update something in my original map? In my index. 123 00:08:57,090 --> 00:09:03,780 I have to update my map right because my map now should also include the revenue column. Because my answer 124 00:09:03,780 --> 00:09:08,990 could be somewhere here, could it be somewhere here, depending on what the user is going to select in 125 00:09:08,990 --> 00:09:10,520 the dropdown. 126 00:09:10,530 --> 00:09:12,780 First thing is to update the map. 127 00:09:12,780 --> 00:09:17,470 The second thing is what about the row argument? Is that OK? It's fine. 128 00:09:17,480 --> 00:09:17,810 Right. 129 00:09:17,810 --> 00:09:23,720 Because I know I should move down this many rows. And then the next question I need to answer is how 130 00:09:23,720 --> 00:09:25,900 many columns do I move? 131 00:09:25,910 --> 00:09:27,950 Well, what does that depend on? 132 00:09:28,820 --> 00:09:32,350 It depends on what the user has selected. 133 00:09:32,720 --> 00:09:36,890 So I'm going to Match again because I need a number back. 134 00:09:36,920 --> 00:09:45,220 I'm going to Match again for this, that's my lookup value. Where am I looking this up? In here. 135 00:09:46,130 --> 00:09:53,330 You see, this range the, the width of my lookup array is the same as the width of my index. 136 00:09:53,460 --> 00:09:58,640 I have to be in sync and then I'm going to get a perfect Match. 137 00:09:58,740 --> 00:10:00,300 Close this. 138 00:10:00,630 --> 00:10:01,790 I think that's it. 139 00:10:02,040 --> 00:10:03,690 And click enter. 140 00:10:03,690 --> 00:10:11,050 What happens, I go for revenue, I get revenue. I change this to 141 00:10:11,280 --> 00:10:14,280 let's go to Hackrr. 142 00:10:14,520 --> 00:10:16,130 Hackrr is the game. 143 00:10:16,170 --> 00:10:27,150 It has this much revenue and how much profit? This much profit. That's how you can use Index and Match 144 00:10:27,480 --> 00:10:37,160 for Matrix type of lookups. What I suggest you do that the next time you come across a lookup issue, 145 00:10:38,180 --> 00:10:39,500 don't use VLookUp, 146 00:10:39,500 --> 00:10:45,020 even if VLookUp will work there. Try to use Index and Match because that's the only way that you're 147 00:10:45,020 --> 00:10:46,390 going to get practice. 148 00:10:46,690 --> 00:10:49,320 And the more practice you get 149 00:10:49,460 --> 00:10:54,600 what happens is that in the moment that you get a more complex Lookup, so let's say your colleagues trying 150 00:10:54,650 --> 00:11:00,450 to do a Vlookup and it's not working and they ask you: "Do you know how to solve this?" And you're going to 151 00:11:00,450 --> 00:11:07,070 be like "Yes, I'm going to use index and Match here". In the next example I'm going to show you how you can 152 00:11:07,070 --> 00:11:14,180 use it to solve more complex problems because in real life you don't have your data generally set up 153 00:11:14,210 --> 00:11:15,430 as simple as this. 154 00:11:15,590 --> 00:11:21,860 You might have it set up like this, where you have more than one header and we are going to see in the 155 00:11:21,860 --> 00:11:23,560 next lecture how to solve this. 14998