Original subtitles

1

In this lecture I'm going to show you how you can use Index and Match to solve complex LOOKUP problems.

2

The thing with Index and Match is that it's not a VLookUp. It's much better than a VLookUp and

3

you are going to come across situations or you have probably come already across situations where VLookUp

4

just wasn't working.

5

It couldn't do the Look-Up that you wanted because your Look-Up problem was too complex.

6

So that's exactly when Index and Match can come to the rescue.

7

It was difficult for me to start using Index and Match. Just like a habit I had to force myself at the

8

beginning to use it until I got the hang of it.

9

Now, what I'm going to do in this lecture is first to explain to you how Index works in easy terms and

10

then I'm going to show you how Match works and then we are going to put these together.

11

So the example I have is a list of divisions, apps, revenue, and profit. The aim of our formula is that

12

we want someone to select an app here, so let's say "Misty Wash" and we want to get the division first.

13

So you can see by the order of these, apps is here, division is here.

14

Would VLookUP work? The classical VLookup is not going to work because you will need to have

15

apps on this side and division on this side.

16

So, that's why Index and Match

17

is great for this.

18

So let me show you what Index does on its own alone.

19

The first argument in Index is the array argument.

20

Think of it like this: Index is like a GPS and for this GPS you need to upload a map on there. Your

21

map is your array.

22

OK, if I highlight this, that's my map. And what map do you give it?

23

Well, the only map it needs is the map that has your answer in it.

24

It doesn't matter what you're lookup problem is.

25

It doesn't matter in this case that we're looking for an app and it's called "Misty Wash".

26

I don't need to include that in my map.

27

I only need to include in the map where my answer is.

28

If my answer was also going to be here or here or here, I have to extend my map.

29

But in this case I know that I want a division and the division is somewhere here.

30

That's all I need to include. The next argument is basically how many rows do you need to

31

go down and how many columns you need to move across.

32

Think of it like the longtitude and latitude in a map. And these arguments are numbers that you give it.

33

Let's say if I say move down two rows, I close the bracket because the last argument you see it's

34

in square brackets.

35

It means it's optional.

36

It's not necessary

37

and in this case anyway I just have one column, so I'm going to put 2. I get "Game"

38

Why?

39

Well, I index what? This area, right?

40

And it counts like this.

41

This is a 1

42

This is a 2.

43

So it returned the second place and that's division.

44

Well, what happens now

45

if I put one in there and I close the bracket? It's still "Game"

46

It's one column.

47

If I put a 0.

48

What happens?

49

It's still "Game"

50

Excel realizes that it's one column but what happens if I put a two here? "#REF!". I'm moving outside

51

my map.

52

If I was going to do that, if I really think that my answer is actually somewhere here.

53

All I have to do is extend my map.

54

Instead of A6 to A15, I'm going to look until B15 and then it works. OK, so that's all there is

55

with index.

56

Now, the part that we want to automate.

57

Now obviously we're not going to put 2 and 1 and the numbers in. The part that we want to automate

58

is the two.

59

OK.

60

Is this row number argument.

61

This is where you need a function that is going to return a number to the Index. Which functions return

62

numbers?

63

Let's think of a few.

64

You have the Count function, right?

65

You have CountA.

66

You have the Row, you have the Column function.

67

Sometimes you could use these as arguments in the index function but in most cases the function that

68

works in harmony with Index, that you're going to need is the Match function.

69

Let's just write here and see what Match does on its own. Match needs a lookup value.

70

What is it looking for?

71

In this case we are looking for "Misty Wash" and it needs the lookup array.

72

So where should it find this?

73

In this case it's here.

74

One thing you need to watch out with the Match function is that it needs a one way street.

75

You cannot give it something like this because then it doesn't know, should it look this way or should

76

it look this way.

77

So it has to be a one way street.

78

So let's go back.

79

OK so that's where we should find it.

80

And then the last argument is the Match type, do you want an exact Match, less than or greater than.

81

In most cases you're going to need an exact Match, so that's like the false argument in VlookUp.

82

If your data was sorted and you're looking for an approximate Match then you're going to need less than

83

or greater than but majority of the cases it's going to be zero.

84

So what am I going to get? 9.

85

What does that mean?

86

That means that Misty wash is the ninth position in here.

87

Is it the ninth position?

88

Yes it is, right?

89

That's exactly the argument that we are going to return to the Index function.

90

Let's type this now, this full formula.

91

First what comes in the index argument? Where we think the answer is.

92

The map that contains the answer and that's that.

93

What is our row argument?

94

Well, we are going to use Match to figure it out for the Index argument and we are going to Match this

95

one.

96

Where in here and we're going to look for an exact Match.

97

Bracket close two times.

98

The only important thing here is that I have the same length. The same array length for both my Index and

99

the Match functions because they need to be in sync.

100

Right?

101

And this gives me "Utility" because if they're not, I'm going to be returning the wrong address to the

102

Index function.

103

Now we're going to do the same thing for profit.

104

We're going to Index. What should we Index right now? This column.

105

That's all I need.

106

And how many rows should I move down?

107

I'm going to use the Match function.

108

What am I looking up?

109

I'm looking up Misty Wash. Where am I looking it up?

110

Only in here.

111

Arrays have the same height and I'm looking for an exact Match. Bracket close, close.

112

And that's my number.

113

That's a simple Index and Match

114

but what if I wanted to switch between profits and revenue here.

115

So let's do something.

116

Let's add a validation to this.

117

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

switch between revenue and profit and this number should obviously change.

119

How do I do that?

120

That's when I need to use the Column argument.

121

Right?

122

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

I have to update my map right because my map now should also include the revenue column. Because my answer

124

could be somewhere here, could it be somewhere here, depending on what the user is going to select in

125

the dropdown.

126

First thing is to update the map.

127

The second thing is what about the row argument? Is that OK? It's fine.

128

Right.

129

Because I know I should move down this many rows. And then the next question I need to answer is how

130

many columns do I move?

131

Well, what does that depend on?

132

It depends on what the user has selected.

133

So I'm going to Match again because I need a number back.

134

I'm going to Match again for this, that's my lookup value. Where am I looking this up? In here.

135

You see, this range the, the width of my lookup array is the same as the width of my index.

136

I have to be in sync and then I'm going to get a perfect Match.

137

Close this.

138

I think that's it.

139

And click enter.

140

What happens, I go for revenue, I get revenue. I change this to

141

let's go to Hackrr.

142

Hackrr is the game.

143

It has this much revenue and how much profit? This much profit. That's how you can use Index and Match

144

for Matrix type of lookups. What I suggest you do that the next time you come across a lookup issue,

145

don't use VLookUp,

146

even if VLookUp will work there. Try to use Index and Match because that's the only way that you're

147

going to get practice.

148

And the more practice you get

149

what happens is that in the moment that you get a more complex Lookup, so let's say your colleagues trying

150

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

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

use it to solve more complex problems because in real life you don't have your data generally set up

153

as simple as this.

154

You might have it set up like this, where you have more than one header and we are going to see in the

155

next lecture how to solve this.