The Excel Index Match function is one of Excel’s most flexible and powerful lookup functions and has several advantages over the traditional VLOOKUP function. The Excel Index Match function is much more flexible than the VLOOKUP function and is able to overcome many of the VLOOKUP’s structural limitations. Index Matching also requires less processing power from your computer, which is very important if you are building a spreadsheet with thousands of lines. However, very few people know how to create an Index Match function, much less use one. In the article below, I will show you how to create Excel Index Match functions and how you can use them to solve complicated lookup problems.
The Excel Index Match Function: A Summary
The Excel Index Match function is actually a combination of two different functions: INDEX and MATCH. The basic way it works is that you define the array with the item you want to pull in the INDEX function, and then you use the MATCH function to identify the row. Here’s the “official” Excel syntax:
Here’s the function in plain English:
How the Function Works in Real Life
The most important advantage of the Excel Index Match function is that, unlike the VLOOKUP function, you can use Index Matching to lookup values to the left and the right of your reference column. In the example below, if you wanted to pull the ID number of a particular fruit using the fruit name in a VLOOKUP, you couldn’t do it. You would have to move the ID column to the right of the Fruit column in order to avoid generating an error with a VLOOKUP.
However, if you use Index Matching (as in the example below), it is very easy to pull values to the left of your reference column.
In this example, the column we want to pull our final result (or value) from is Column A, ID. So, that goes into the first part of our function. Then our lookup value is simply D2, which contains the word Apples. Next, we define the column we want to find the lookup value in as Column B, Fruit. Finally, we set the last function option as 0, which tells Excel we want an exact match to our lookup value in the lookup. Here’s what our final formula looks like:
The formula works by identifying the row number in the lookup column (in this example Column B) by finding the lookup value in that column. It then returns whatever row number the lookup value is in as the result. So, in this example, “Apple” was in Row 2 of the lookup column. So, the MATCH function returns a value of 2. Then, in the INDEX portion of the function, Excel takes the row number identified in the MATCH function (in our example, 2) and pulls whatever value is in row 2 of the column you want to pull a value out of. So, in this example, since the column we want to pull a value out of is Column A and the row number (defined by the MATCH function) is 2, Excel pulled the item in row 2 of Column A, “1”. Below is a graphic demonstration of how the function operates:
Once you are able to master Index Matching, you will be able to perform much more complicated data lookups than you would with a simple VLOOKUP.
Don’t Stop Here
There are many more valuable lookup functions that you can use in Excel beyond VLOOKUPs and Index Matching. Sign up for our free email newsletter to learn more about these functions and other advanced functionality in Excel. You’ll also get exclusive discounts for our training classes and webinars.