Master Excel's Index Match function to efficiently retrieve data from tables, surpassing VLOOKUP's limitations. Learn how combining INDEX and MATCH improves flexibility and accuracy in data lookup tasks.
Key Insights
- The Index Match function in Excel merges the INDEX and MATCH functions, enabling users to search data both to the left and right of reference columns, a functionality not possible with VLOOKUP.
- Naming ranges (such as calling a column "part name") simplifies Index Match formulas, allowing easy identification and referencing of columns or arrays within Excel worksheets.
- Using INDEX for defining the search array and MATCH to identify the matching row number, users can efficiently pinpoint exact data locations, demonstrated in the example of locating SKU08 and identifying "unicorn" as the corresponding item.
This lesson is a preview from our Advanced Excel Course Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
Index match. INDEX and MATCH efficiently return a value in a row or column that matches a value in another row or a 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. We're going to start off by having you use the MATCH function to find the row number for SKU-08.
Before we begin, I'll briefly go over the arguments. 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 SKU-08. So I'm going to type in =MATCH. I'm going to select the lookup value.
Where I'm going to find SKU-08 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 SKU-08 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 arrays, 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 part name. I'll press ENTER.
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. So 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 one, two, three, four, five, six, seven, eight, and what they would do is they would type eight here, and 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 SKU-08. Ironically, that is the same position, SKU-08 is in the same position as unicorn, and eight matches the number of rows we need to go down. 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 one, 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. Essentially, that is what you're doing with the INDEX MATCH function. I'm going to go and type =INDEX.
Now the first building is going to be part name. We can pretend that that's building one. 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, and then what do we want to match? SKU-08. So this is how it's very similar to a VLOOKUP. Then I'll enter a comma.
Where am I going to find SKU-08? I'm going to find it in building two or in column two, and if I find an exact match for SKU-08, I should get the right row that matches along with the part name row to give me what I'm looking for, which in this case is unicorn. 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 you how you could use INDEX MATCH using just two columns.
That is the INDEX MATCH function.