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:

Cell I22 = INDEX(C22:F31…

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:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31…

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:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,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.

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21…

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:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F21,0))

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.

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.

Cell I26 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))

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:

1. Click on cell I26.
2. Go to the FORMULAS
3. 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:

Cell I26 {= INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))}

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.

Feel free to Download the Workbook HERE.

Here’s Part 1 of this guide on INDEX & MATCH.

Use these techniques in your own reports

Unbeatable value!