Excel INDEX and MATCH Functions

Excel’s INDEX and MATCH functions have many uses, we’ll look at each function in isolation to understand how they work, and then we’ll use them together as an alternative to the VLOOKUP formula.

How the INDEX function works:

The INDEX function returns the value at the intersection of a column and a row.

The syntax for the INDEX function is:

=INDEX(array, row_num,[column_num])

In English:

=INDEX( the range of your table, the row number of the table that your data is in, the column number of the table that your data is in)

INDEX will return the value that it is in the cell at the intersection of the row and column you specify.

For example, looking at the table below in the range B17:F24 we can use INDEX to return the number of program views for Bat Man in the North region with a formula as follows:

=INDEX(B17:F24,2,3)

The result returned is 91.

On its own the INDEX function is pretty inflexible because you have to hard key the row and column number, and that’s why it works better with the MATCH function.

Note: You may have noticed that the INDEX function works in a similar way to the OFFSET function, in fact you can often interchange them and achieve the same results.

How the MATCH function works:

The MATCH function finds the position of a value in a list.  The list can either be in a row or a column.

The syntax for the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

Now I don’t want to go all syntaxy (real word  ) on you, but I’d like to point out some important features of the [match_type] argument:

• The match_type argument specifies how Excel matches the lookup_value with values in lookup_array. You can choose from -1, 0 or 1 (1 is the default)


• [match_type] is an optional argument, hence the square brackets. If you leave it out Excel will use the default of 1, which means it will find the largest value that is <= to the lookup_value. The values in the lookup_array must be in ascending order when using 1 or omitting this argument..


• 0 will find the first value that is exactly equal to the lookup_value. The values in the lookup_array can be in any order.


• -1 finds the smallest value that is >= to the lookup_value. The values in the lookup_array must be in descending order, for example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.

Ok, that’s enough of the syntax.

In English and using the previous example:

=MATCH(find what row Bat Man is on, in the column range B17:B24, match it exactly (for this we’ll use 0 as our argument))

The result is row 2.

We can also use MATCH to find the column number like this:

=MATCH(find what column North is in, in the row range B17:F17, match it exactly (again we’ll use 0 as our argument))

The result is column 3.

So in summary, the INDEX function returns the value in the cell you specify, and the MATCH function tells you the column or row number for the value you are looking up.

Using the INDEX and MATCH functions together:

The INDEX and MATCH functions are a popular alternative to the VLOOKUP. Even though I still prefer VLOOKUP as it’s more straight forward to use, there are certain things the INDEX + MATCH functions can do that VLOOKUP can’t.  More on that later.

Using the above example data we’ll use the INDEX and MATCH functions to find the program views for Bat Man in the East region.

=INDEX( the range of your table, replace this with a MATCH function to find the row number for Bat Man, replace this with a MATCH function to find the column number for East)

The formula will read like this:

=INDEX( return the value in the table range B17:F24 in the cell that is at the intersection of, MATCH( the row Bat Man is on) and, MATCH(the column East is in)

The formula looks like this:

=INDEX($B$18:$F$24,MATCH(“Bat Man”,$B$18:$B$24,0), MATCH(“East”,$B$17:$F$17,0))

So why would you put yourself through all that rigmarole when VLOOKUP can do the same job.

Reasons why you’d use INDEX and MATCH rather than VLOOKUP

1)      VLOOKUP can’t go left.

Taking the table below, let’s say you wanted to find out what program was on the Krafty Kids channel.

VLOOKUP can’t do this because you’d be asking it to find Krafty Kids and then return the value in column B to the left, and VLOOKUP can only look to the right.

In comes INDEX and MATCH with a formula like this:

=INDEX($B$33:$D$40,MATCH(“Krafty Kids”,$C$33:$C$40,0), MATCH(“Program”,$B$33:$D$33,0))

And you get the answer; ‘Mr Maker’.

2)      Two way lookup.

The table below has a drop down list in B1 that enables me to choose the Sales Person from the table, and a drop down list in A2 for the region.  In B2 I’ve got an INDEX + MATCH formula that returns the sales that match my two criteria.

=INDEX(A4:J10,MATCH(A2,A4:A10,0),MATCH(B1,A4:J4,0))

Note: An alternative is to use a VLOOKUP and replace the hard keyed column number with a MATCH formula like this:

=VLOOKUP(A2,$A$4:$J$10,MATCH(B1,A4:J4,0),FALSE)

Ways to improve these formulas:

1)      Use named ranges instead of $C$33:$C$40 etc. to make formulas more intuitive and quicker to create.

2)      An alternative to using a named range is to convert the data to an Excel Table whereby Excel automatically gives the table a named range.

3)      If there is nothing else in the columns other than your table you could use column references like this C:C which will search the whole column.

Share the knowledge with your friends and colleagues on Twitter, and Facebook etc. using the shortcuts below.