Using MATCH() and INDEX() in Excel

I am not sure I have ever seen an instance where these 2 functions are NOT used together!

1. MATCH()

=MATCH(lookup_value, lookup_array, [match_type])

Returns the position of a cell in a row or column.

2. INDEX()

=INDEX(array, row_num, [column_num])

There are two ways in which the INDEX() function can be used; we are using the method that returns the value of a cell in a table based on the column and row number.

3. Together

Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.

If we look at the following simple table, we can easily look up the distance from Cape Town to Durban by following the Cape Town row to where it intersects the Durban column.

The MATCH() function will give the position, or index, of a value in a range, or array. This allows us to find the index of the row in which Cape Town appears and the index of the column where Durban appears.

Using those 2 indexes we can use the MATCH() function to return the distance from the table.

Enter the data in the above table in a spreadsheet and then create the functions paying careful attention to the arguments and their descriptions as you work.

4. Nested

The MATCH() functions can be nested in the INDEX() functions as arguments. The easiest way to do this (if you have followed the building blocks approach above) is as follows:

1. Click on cell C2 and copy the function without the = sign
2. Click on cell B2
3. Click in the Formula Bar and select the first argument of the INDEX() function, C8
4. Delete the existing argument and paste the first MATCH() formula from C8 in its place
5. Repeat the process, replacing the third argument of the INDEX() functions with the MATCH() function copied from cell D9.

References:

1. Microsoft Support. (2024) INDEX function. Available at: https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd (Accessed: 7 May 2024).
2. Microsoft Support. (2024) MATCH function Available at: https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a (Accessed: 7 May 2024).