INDEX and MATCH Example
Familiarity with the VLOOKUP function can cause you to restrict INDEX and MATCH to less than they can be. The example spreadsheet below bears this out.
The formula for Fiscal QTR takes the Month in cell E2 as input, finds that value in the Month column, then returns the Fiscal QTR in the same row. The first formula is:
This INDEX formula uses the array range A2:B13 for the first argument, which is what you may use if familiar with VLOOKUP. But this requires that both the second and third arguments are used (row and column position).
The MATCH function looks to cell E2, finds the exact matching value in the Month range A2:A13, and returns the row number 7 to the INDEX Function for it’s second argument. The number 2 is input for the third (column) argument of the INDEX Function. The INDEX function returns the value 1 from row 7, column 2 of the A2:B13 range.
The second formula:
is different in that INDEX uses a one-column range B2:B13, so only a row number argument is required. The MATCH function looks at Jul in cell E2 and finds a match in range A2:A13, returning the number 7 for the INDEX row number argument.
A Grade Lookup Example with INDEX and MATCH
Here’s a student grade lookup formula that relies on the match type argument of the MATCH function to be 1, which finds the largest value, less than or equal to the lookup value.
The formula in cell C2 is:
The INDEX function uses array range F5:F9 and the MATCH function array range is B2:E9. (The dollar signs ($) indicate absolute values because I copy the formula down).
The MATCH function looks to cell B2, which has the value 100, then finds an approximate match of 90 in row 5 in the range E5:E9. The 1 indicates this will be an approximate match. The INDEX function then returns the cell in row 5 of the F5:F9 range, which is the text value A.