How to Use INDEX MATCH
Any legitimate Excel user has used VLOOKUP and knows the syntax by heart. (Lookup Value, Array, Column, etc.) But many of these same users have never used INDEX MATCH before. INDEX MATCH is one of several lookup formulas available in Excel. It has certain features that make it superior to VLOOKUP in many situations. Regardless of which method you think is better, (I have my opinion) it is definitely worth it to learn both formulas and have both at your disposal.
Please note that INDEX MATCH is designed for vertical lookups, which is the task that VLOOKUP performs. If you need to perform a matrix lookup, consider using one of the more powerful Excel lookup formula combinations such as INDEX MATCH MATCH, OFFSET MATCH MATCH, VLOOKUP MATCH, or VLOOKUP HLOOKUP.
The VLOOKUP function
First our point of reference: the trusty VLOOKUP formula. VLOOKUP returns a VALUE based on a defined array and column reference. The syntax from Excel is as follows:
=VLOOKUP ( lookup value , lookup array , column , range lookup )
Below is an example of using VLOOKUP to return the value “Shirts” based on the lookup value “1089.” (Please note that, because we input false for that portion of the syntax, we are not using the range lookup feature of VLOOKUP in this example)
Our goal was to lookup the Product of ID “1089” and VLOOKUP did that without a hitch. So how does INDEX MATCH replicate that functionality?
The INDEX function
The basic INDEX function returns a VALUE based on a defined array / column and a row number. The syntax from Excel is as follows:
=INDEX ( array , row number )
Below is an example of using INDEX to return the value “Shirts,” assuming that you already know that the value is three cells down on your defined array.
(you also have the option to specify column number, but that isn’t relevant in a basic INDEX MATCH formula)
If you go 3 cells down in the INDEX array, you get the value “Shirts.” But the problem we have when trying to do a lookup is that we typically don’t know what position our return value is located, which in this case is 3. The “3” needs to come from another formula.
The MATCH function
The basic MATCH function returns a NUMBER based on the relative position of a lookup value within a defined array / column. The syntax from Excel is as follows:
=MATCH ( lookup value , lookup array , match type )
Below is an example of using the MATCH formula to return the position of “1089” within our column reference.
Since “1089” is three cells down in the array, the value “3” is returned.
INDEX MATCH
When we combine both the INDEX formula and the MATCH formula, the number that the MATCH formula returns becomes the row number for your INDEX formula.
=INDEX ( array , MATCH formula )
Below is an example of using the INDEX MATCH to return “Product Type” for our lookup value.
As you can see, it returns the same value we got from VLOOKUP.
Differences Between the Formulas
One of the key difference with INDEX MATCH is that, rather than selecting an entire array table, you are only selecting the lookup column and the return column of what would be a VLOOKUP array. While not a big deal when it comes to simple lookups, this can definitely become a factor if you are dealing with large files that have thousands of lookups. By limiting your arrays to only the lookup and return columns, you reduce the processing load on Excel. The difference is illustrated below.
The other key difference is that INDEX MATCH formulas work as a right to left lookup, whereas VLOOKUP only works left to right. As you can see in the example below, I can use INDEX MATCH to lookup a value that is to the right of my return value using INDEX MATCH. This is not possible with the VLOOKUP formula, as you would have to rearrange your data set, or copy your lookup column so that it is always to the left of your return value.
I reversed our formula by looking up the ID of the “Shirts” Product Type. I just changed our lookup value and swapped the lookup and return columns.
Remembering It All
A lot of Excel websites do a poor job of explaining how to use INDEX MATCH. I’m not saying my way is any better, but here’s any easy way to remember how to use the formula, if you don’t want to mess with the detailed explanations and know how to use VLOOKUP. Here’s how I think about the formula as I’m typing it in:
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))
Lookup multiple criteria in rows or columns
Some reports involve the need to find a value from a source table using multiple criteria in rows and columns. In this example, we have a table containing both the actual and budget revenues and profits for each application as shown below:
From this data, you need to create a report that returns the value corresponding to three criteria that the user selects:
- Actual or Budget
- Revenue or Profit
- App name
This becomes the matrix lookup, however, unlike the regular cases, this has more than one header (Row 20: Actual or Budget, Row 21: Revenue or Profit).
Index Match approach
When using the Index Match approach, the first thing you identify is the map or the area that contains the answer. Since this report needs to display the corresponding number from the criteria, the array used is C22:F31. The function at this point is written as:
Had it required to display a value other than the numbers, you will need to include the cells containing those values as well. For example, if you also need to display the Division, the array becomes A22:F31 instead of C22:F31, since A21:A31 contains the Division values.
For the next argument in the INDEX() function, you need to determine how many rows you need to go down. The MATCH() function is used to find at what row number the lookup value is found. Since we want to use the selected App in cell H22 and matching it with cells B22:B31 which contains the Apps, the function now becomes:
There are three options for the match type argument:
- 1 = less than
- 0 = exact match
- -1 = greater than
Since we want an exact match, we use 0:
Find the column that corresponds to both the criteria selected in cells I20 and I21. Another MATCH() function can be used here. Unlike the regular case, your lookup value is derived from two cells. Combine these criteria using the & symbol.
The lookup array for this lookup value is found at C20:F21, which has two rows instead of one.
Initially, you would think about using C20:F21 as your lookup array and write the formula as:
However, instead of displaying the value corresponding to the criteria, it results to an error. This is because MATCH() can only handle a single row or column and cannot handle a combination of rows and columns, such as C20:F21 because it will not know in which direction to move. Had it been a single row such as C20:F20 or C21:F21, it would have worked out fine.
There are multiple ways to resolve this. One way to do it is to revise the table by separating it into two—one containing only the Actual values, and the other containing only the Budget values, and then doing a VLOOKUP to change the source table array depending on the selected criteria. However, it is also possible to resolve this problem without changing the format of the current table. There are three ways to do it.
Method 1: Using helper cells
Since the MATCH() function can only handle single rows and columns, the simplest way to resolve the problem we had earlier is to use helper cells that combine the values in rows 20 and 21 into one row instead of two. This combination becomes the unique identifier of each column.
You will see that C19 now contains ActualRevenue. Drag this formula to the right until cell F19.
Go back to the last MATCH() function that used cells I20 and I21 as the lookup value. Instead of using C20:F21 as the lookup array, you can now use the new helper cells C19:F19.
This formula displays the value that corresponds to the selected criteria in cells I20, I21 and H22.
Method 2: Using CTRL + SHIFT + ENTER (CSE)
There is another approach that eliminates the use of helper cells. This involves generating an array for the MATCH() function by pressing the keys CTRL + SHIFT + ENTER (CSE).
The MATCH() function is not meant to handle array functions, rather, it looks at things one cell at a time instead of holding things in memory and handle them. Some functions that can handle arrays are:
- INDEX()
- SUMPRODUCT()
- VLOOKUP()
- HLOOKUP()
- AGGREGATE()
However, clicking on CSE on a MATCH() function enables it to handle arrays. Write the same function as above, only instead of using the helper cells, revise the last MATCH() function to combine cells C20:F20 and C21:F21 using the & symbol.
After pressing ENTER, you will notice that it results to an error because there is no instruction telling the MATCH() function to keep the values in memory. To see the step-by-step calculation of the function resulting to the error:
- Click on cell I26.
- Go to the FORMULAS
- Click on EVALUATE FORMULA.
You will see the function written on the white space. Each click on the EVALUATE button will show you the calculation step-by-step. It first finds the value of cell H22, and finds the row containing that App, followed by finding the values in cells I20 and I21 and combining them. You will notice that the value returned by C20:F20 and C21:F21 are #VALUE! The function notices that you are trying to combine things and it is confused because it does not know how to handle such instances.
Click on CLOSE to exit the window. Go back to cell I26. Click on the function in the formula bar and instead of pressing the ENTER key, click CTRL + SHIFT + ENTER.
Notice that it places < >at the start and end of the function and becomes:
It now displays the value that corresponds to the three criteria selected.
Click on cell I26 again and go to EVALUATE FORMULA to see how this differs from the previous one. Instead of returning #VALUE! for both C20:F20 and C21:F21, it is now able to find the values in those cells and combine them. The < >gives an instruction to the MATCH() function to keep the values in memory, which makes it easier for it to take C20 and combine it with C21 to give ActualRevenue, D20 with D21 to give ActualProfit, and so on.
It then finds the column number that corresponds to the criteria, “BudgetRevenue”, which is 3. The INDEX() function is now able to find the value using the numbers from the row and column arguments.
This approach is a simple way of writing but it can be confusing for a lot of people. It is only suitable when you are writing the formulas for your own use. Otherwise, if you have other users, it is very likely that those users do not know array functions. They might see the array, click on the formula, inspect it a bit, and press ENTER. Since they press ENTER instead of CSE, the formula will then result to an error again. In such cases, it would be best to avoid this approach.
Method 3: Using two INDEX() functions
The third approach does not require helper cells nor CSE, but replaces the last MATCH() function with an INDIRECT() function.
Start off using the same functions for the most part except for the last MATCH() function:
In order to avoid the use of CSE, we need to use a function that can handle arrays. The INDEX() function can be used by putting the entire lookup area inside the INDEX() function. To do this, replace the last MATCH() function in Method 2 with:
It is important to comply with the syntax of the INDEX() function. The row_num argument is mandatory. Instead of leaving the formula up until C21:F21, you need to specify a row number. In this case, you want to tell the function to take every single row. There are two ways to do that:
- Use the Excel separator (,) and leave it empty
- Use 0 as the row number
Next, comply with the syntax of the MATCH() function where you need to specify the match type. Again, use 0 for an exact match. The final formula becomes:
This gives you the same result as the first two methods. There are other ways to resolve this problem but I find these three to be the simplest ways.
