The lookup array for this lookup value is found at C20:F21, which has two rows instead of one. Unlike the regular case, your lookup value is derived from two cells. Another MATCH() function can be used here. Since we want an exact match, we use 0: Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0)įind the column that corresponds to both the criteria selected in cells I20 and I21. There are three options for the match type argument: 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… The MATCH() function is used to find at what row number the lookup value is found. 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.įor the next argument in the INDEX() function, you need to determine how many rows you need to go down. Had it required to display a value other than the numbers, you will need to include the cells containing those values as well. The function at this point is written as: Cell I22 = INDEX(C22:F31… Since this report needs to display the corresponding number from the criteria, the array used is C22:F31. When using the Index Match approach, the first thing you identify is the map or the area that contains the answer.
0 kommentar(er)
