In this lecture, I'm going to show you how you can use index and match to solve complex lookup problems. The thing with INDEX and MATCH is that it's not a VLOOKUP, it's much better than a VLOOKUP. And you are going to come across situations or you've probably come already across situations where VLOOKUP just wasn't working.
It couldn't do the lookup that you wanted, because your lookup problem was too complex. That's exactly when index and match can come to the rescue. It was difficult for me to start using index and match.
Just like a habit, I had to force myself at the beginning to use it until I got the hang of it. Now, what I'm going in this lecture is first, to explain to you how index works in easy terms. And then I'm going to show you how match works.
And then we're going to put these together. So, the example I have is list of divisions, apps, revenue, and profits. The aim of our formula is that we want someone to select an app here, so let's say Misty Wash and we want to get the division first.
So you can see that the order of these, apps is here, division is here, right? Would VLOOKUP work? The classical VLOOKUP is not going to work, right?
Because you will need to have apps on this side and division on this side. That's why index and match is great for this. Let me show you what index does on its own, alone.
The first argument in index is the array argument. Think of it like this: INDEX is like a GPS. For this GPS you need to upload a map on there.
Your map is your array. Okay, so if i highlight this, that's my map. And what map do you give it?
Well, the only map it needs is the map that has your answer in it. It doesn't matter what your lookup problem is, it doesn't matter in this case that we're looking for an app and it's called Misty Wash, I don't need to include that in my map. I only need to include in the map where my answer is.
If my answer was also going to be here or here or here, I have to extend my map. But in this case, I know that I wanted division. And the division is somewhere here.
That's all I need to include. Okay, the next argument is basically how many rows do you need to go down, and how many columns do you need to move across? Think of it like the longitude and latitude in a map.
And these arguments are numbers that you give it. If I say move down two rows. I close the bracket, because the last argument, you see it's in square brackets, it means it's optional; it's not necessary.
And in this case anyway, I just have one column, so I'm going to put two. Okay, I get Game. Why?
Well, I indexed what? This area, right? And it counts like this: This is a one, this is a two.
If it returns the second place, and that's division. Well, what happens now if I put one in there and I close the bracket? It's still Game.
It's one column, right? If I put a zero, what happens? It's still Game.
Excel realizes that it's one column. But what happens if I put a two here? Reference.
I'm moving outside my map. Okay, if I was going to do that, if I really think that my answer is actually somewhere here, all I have to do is extend my map. Instead of A6 to A15, I'm going to look until B15 and then it works.
That's all there is with index. Now, the part that we want to automate . .
. Now, obviously we're not going to input two and ones as the numbers here. The part that we want to automate is the two.
It's this row number argument. This is where you need a function that is going to return a number to the index. Which functions return numbers?
Let's think of a few. You have the COUNT function, right? You have COUNTA.
You have the have the row, you have the column functions. Sometimes you could use these as arguments in the index function, but in most cases, the function that works in harmony with INDEX, that you're going to need, is the MATCH function. Let's just write here.
. . and see what match does on its own.
Match needs a lookup value. What is looking for? In this case, we're looking for Misty Wash.
And it needs to lookup array. Where should it find this? In this case, it's here.
One thing you need to watch out with the match function is that it needs a one-way street. You cannot give it something like this, because then it doesn't know should it look this way or should it look this way? It has to be a one-way street.
Let's go back. That's where it should find it. And then the last argument is the match type.
Do you want an exact match, less than, or greater than? In most case, you're going to need an exact match. That's like the false argument in VLOOKUP.
If your data was sorted and you were looking for an approximate match, then you're going to need less than or greater than. But, majority of the cases, it's going to be zero. What am I going to get?
Nine. What does that mean? That means that Misty Wash is the ninth position in here.
Is is the ninth position? Yes, it is, right? That's exactly the argument that we're going to return to the index function.
Let's type this now, the full formula. First, what comes in the index argument? Where we think the answer is, right?
The map that contains the answer. And that's that. What is our row argument?
Well, we're going to use match to figure it out for the index argument. And we're going to match this one. Where?
In here, and we're going to look for an exact match. Bracket close two times. Now, the only important thing here is that I have the same length, the same array length, for both my index and the match functions, because they need to be in sync, right?
And this gives me Utility. Because if they're not, I'm going to be returning the wrong address to the index function. Now we're going to do the same thing for profit.
We're going to index. What should I index right now? This column, right?
That's all I need. And how many rows should I move down? I'm going to use the match function.
What am I looking up? I'm looking up Misty Wash. Where am I looking it up?
Well, only in here. Okay, arrays have the same height. And I'm looking for an exact match.
Bracket, close, close. And that's my number. That's a simple INDEX and MATCH, but what if I wanted to switch between profit and revenue here?
Let's do something. Let's add a validation to this. I'm going to put data validation, list, and I want these two.
Here what I want to do is to be able to switch between revenue and profit, and this number should obviously change. How do I do that? That's when I need to use the column argument, right?
But, is that the only thing I need to add, or do I need to update something in my original map in my index? I have to update my map, right? Because my map now should also include the revenue column, because my answer could be somewhere here, could be somewhere here, depending on what the user's going to select in the dropdown.
First thing is to update the map. The second thing is what about the row argument? Is that okay?
It's fine, right? Because I know I should move down this many rows. And then the next question I need to answer is how many columns do I move?
Well, what does that depend on? It depends on what the user has selected. I'm going to match again, because I need a number back, right?
I'm going to match again for this, that's my lookup value. Where am I looking this up? In here.
And you see this range, the width of my lookup array is the same as the width of my index. I have to be in sync. And then I'm going to get a perfect match, close this.
I think that's it. And click enter. Now, what happens?
I go for revenue, I get revenue. I change this to, let's go to Hackrr. Hackrr is a game.
It has this much revenue. And how much profit? This much profit.
That's how you can use index and match for matrix type of lookups. What I suggest you do is that the next time you come across a lookup issue, don't use VLOOKUP, even if VLOOKUP will work there. Try to use index and match, because that's the only way that you're going to get practice.
And the more practice you get, what happens is that then the moment that you get a more complex lookup, let's say your colleague is trying 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 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 use it to solve more complex problems, because in real life, you don't have your data generally set up as simple as this. You might have it set up like this where you have more than one header. And we're going to see in the next lecture how to solve this.
I hope you enjoyed this lecture and don't forget to subscribe to get notifications when I put out more videos that are like this one.
