How to Use INDEX and MATCH Functions in Excel?

Have you ever used the Index and Match formulas of Excel? Well, both of these formulas are used to perform lookups. They can even be used together which will let you perform the two-way lookup.

INDEX function

The INDEX function returns a value or a reference to a value that occurs at a specific position in the provided reference range.

The INDEX function has two forms:

1. Array Form
   The array form of the INDEX function returns a value or a reference to a value. The position of the value in the provided array is specified by the row and column number indexes.
   Syntax
   INDEX(array, row_num, [column_num])

2. Reference form
   The reference form of the INDEX function returns a value of the cell that is present at the intersection of specified row and column indexes.
   If there are more than one reference area then this formula allows you to select the area to look into.
   Syntax
   INDEX(reference, row_num, [column_num], [area_num])

MATCH function

The match function returns the position of an item in the specified range.

Syntax
MATCH(lookup_value, lookup_array, [match_type])

In the section ahead we will be discussing the use of both of these formulae with the help of an example.

Why do we require MATCH function along with INDEX function?

To evaluate the INDEX function, we have to provide the element’s exact row and column number in the specified range. But you hardly remember the position of an element in a spreadsheet. The MATCH function provides the position of an element in the specified range.

That’s why we use the MATCH function along with the INDEX function.

Steps to INDEX and MATCH Functions in Excel:

INDEX Function (Array Form)

We have data of some employees and their sales record in the North, East, West and South region.

Now let us apply the INDEX function to this data. Simply we would write:

=INDEX(A3:A15, 4, 1)

Here A3:A15 specifies the array range where we have to look for the value that is present at the intersection of the 4^th row and 1^st column as specified in the formula. And the result for this formula is:

As in the provided array at the 4^th row and 1^st column the value is “Charles”.

While specifying the row and column in the provided array if you go beyond the array range you will receive a #REF! error.

Example 1: Find out the sales of Charles in the East and South region.

The INDEX function to find the answer to the above query for the East region is:

=INDEX(B3:E15, 4, 2)

This query will search the 4^th row and 2^nd column of the array B3:E15. As 4^th row in the provided range has data related to Charles and the 2^nd column has a sales report of the East region.

For South region:

=INDEX(B3:E15, 4, 4)

MATCH Function

Example 2: Find out the position of Charles in the data given above.

The MATCH function for the above query is:

=MATCH(A6, A3:A15, 0)

Here the A6 is a cell where Charles is written, A3:A15 is a range where we have to search for Charles and 0 ensures the exact match.

The MATCH function can also be written as:

=MATCH(“Charles”, A3:A15, 0)

INDEX and MATCH Function

Example 3: Find out the sales of Charles in the East region. Use MATCH along with the INDEX function.

= INDEX(B3:E15, MATCH(A6, A3:A15,0), MATCH(C2, B2:E2,0))

MATCH(A6, A3:A15,0) this MATCH function evaluates the position of “Charles”. And the evaluated value act as a row number for the INDEX function.

MATCH(C2, B2:E2,0) ) this MATCH function evaluates the position of “East”. And the evaluated value act as a column number for the INDEX function.

Example 4: Find out the sales of Charles in the South region. Use MATCH along with the INDEX function.

= INDEX(B3:E15, MATCH(A6, A3:A15,0), MATCH(E2, B2:E2,0))

INDEX Function (Reference Form)

If there are multiple reference ranges, you can specify from which range you want the answer.

Example 5: Find out sales value at the intersection of the 2^nd row and 2^nd column in the second reference area.

=INDEX((B4:E6, I4:L6, B11:E13, I11:L13), 2, 2, 2)

Here, B4:E6, I4:L6, B11:E13 and I11:L13 are the four non-adjacent reference ranges, where we have to look for the intersection of the 2^nd row and 2^nd column in the second reference area.

Example 6: Find out sales value at the intersection of 2^nd row and 3^rd column in the fourth reference area.

=INDEX((B4:E6, I4:L6, B11:E13, I11:L13), 2, 2, 2)

Here, B4:E6, I4:L6, B11:E13 and I11:L13 are the four non-adjacent reference ranges, where we have to look for the intersection of 2^nd row and 3^rd column in the fourth reference area.

So this is all how you can use INDEX and MATCH function Excel.