Excel Functions: INDEX + MATCH

The Excel function INDEX used with the function MATCH allows for value searching in an array.

Prerequisites:

Before proceeding, consult the tutorial on the function INDEX as well as that of the function MATCH.

To help you understand better, the example used here is a combination of the examples from the 2 indicated tutorials, so their reading is highly recommended.


Combination INDEX + MATCH

If the MATCH function returns the row number:

=INDEX(array, MATCH(lookup_value, lookup_array, 0), col_no)

If the MATCH function returns the column number:

=INDEX(array, row_no, MATCH(lookup_value, lookup_array, 0))

Example of use

The goal here is to find the case number as well as the number of points for the searched location. The user should be able to enter the city in the green part and then see the result of their search in the blue part:

excel function index match

First, select the INDEX function and enter:

index match function

Position yourself in Row_num and add the MATCH function:

index match functions

For the MATCH function to return the row number of the city, enter:

=INDEX(A2:C11,MATCH(E2,B2:B11,0),1)
index match

The case number of the city is then displayed:

excel function index match table

To then display the points, just copy the formula and modify the column number (replace 1 with 3):

excel function index match example
If needed, you can download the Excel file used here: index-match.xlsx