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.