Excel Index Match

INDEX-MATCH and other advanced nested functions are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Index Match

INDEX and MATCH efficiently return a value in a row or column that matches a value in another row or column. INDEX MATCH is a mix of two functions: the INDEX function and the MATCH function. 

The INDEX function outlines the area to be searched, and MATCH determines the appropriate row or column number where that value will be found. INDEX, in combination with the MATCH function, is superior to VLOOKUP. It allows you to look up information either to the left or to the right of the LOOKUP column, something you can't do when you're using VLOOKUP because you must always look up information from left to right. 

INDEX MATCH gives you the freedom to look up information in either direction. So let's take a look at an example. 

Example 

We're going to start off by having you use the MATCH function to find the row number for SKU08. Before we do, I'll briefly go over the argument. Again, the array is the information that is going to be searched, and within that array, you will find your value. An array can be a column, it can be a row, it can even be a table. 

Now, the row number is the row position of the value that you're looking for. You can find a matching value in another row or column to help determine what that row number is. 

So in this exercise, we're going to take a look at INDEX MATCH, and we're just going to use the row part of the INDEX function with the MATCH function. We want to find the row position for SKU08, so I'm going to type in equal, MATCH. I'm going to select the LOOKUP value. Where I'm going to find SKU08 is in this column right here. Then I'll enter a comma, and then I will say I want an exact match. Press enter and I get eight. Shouldn't be too much of a surprise because SKU08 is in the eighth row of that column. 

Now, one of the things you can do to help you with your INDEX MATCH function when you're working with a range, you can name the range. I'm going to do that here, and I'm simply going to call this column "Part Name." That's appropriate because that is the name of the column. So I'll call this "partname." Press enter. 

Before INDEX MATCH 

Now, I'm going to say that before INDEX MATCH came along, Microsoft pioneered a revolutionary way of finding values before INDEX MATCH, and I call this "Index Human."

The way it works is you'll index a particular area. In this case, I'm looking for a part name, so I'll just type that. And then when I enter a comma, Microsoft would have a human being count the number of rows a person would need to go down this column to find the value that you're looking for. In this case, I'm looking for "unicorn." So someone would count to eight, and then they would type "eight" here. When they press Enter, they would go down eight rows in that column. 

Now, this technique wasn't practical because people aren't always available to count for you. So Microsoft decided, you know what, we need a function that doesn't require a human being to do the counting. Therefore, they invented MATCH. 

In fact, you can see that you already used MATCH to find the row number for SKU08. Ironically, SKU08 is in the same position as Unicorn, and eight matches the number of rows we need to go down. 

An Analogy 

So the analogy I'll use here is you can pretend that you're planning to meet someone and they give you an address, but they forgot to tell you what building they're located in. They're actually in Building 1 and they're on this floor right here. 

Now, you're running late and you're trying to get to the building. You're not sure which one to go into. You go into the second building, and then you try to find the person and you realize they did send you information and you are now in the wrong building. So you call your friend and tell them, you know, I'm in the wrong building. I'm probably going to need to step out of the building, and then go to your building to locate you, so it might take me a while. 

At which point your friend says, hey, you don't have to do that. These buildings are connected. What you can do is go to the same row, or floor, that I am in the other building and we can meet in the middle. 

INDEX MATCH 

Essentially, that is what you're doing with the INDEX MATCH function. I'm going to go and type equal, INDEX. Now, the first building is going to be "partname." We can pretend that that's Building 1. Then I'll enter a comma and now I'll use the row number attribute in the INDEX function, but I'll use MATCH to figure out the right row number. Remember, we already did this match. 

Then what do we want to match? SKU08. So this is how it's very similar to a VLOOKUP. Then I'll enter a comma. Where am I going to find SKU08? I'm going to find it in Building 2 or in Column 2. And if I find an exact match for SKU08, I should get the right floor or row that matches along with the Part Name row to give me what I'm looking for, which in this case is unicorn. 

Recap 

So that's an example of INDEX MATCH where you're using just the row attribute of the INDEX function. If you're using the column attribute, well then you're doing a two-way LOOKUP. But for this exercise, we simply wanted to show how you could use INDEX MATCH using just two columns. That is the INDEX MATCH function.