Hi ,

INDEX and MATCH Functions

In this tutorial, we'll dive into the powerful Excel INDEX and MATCH functions, which are essential for manipulating and analyzing large sets of data.

We'll start by exploring what these functions do and how they retrieve specific information from a table, and then we'll write INDEX and MATCH formulas together as an alternative to the VLOOKUP formula. We'll also cover some practical use cases for INDEX and MATCH formulas.

Video : INDEX and MATCH Functions

Download the Example Excel File

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(reference, row_num,[column_num], [area_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, and if your reference specifies two or more ranges (areas) then specify which area*)

*Typically only one area is specified so the area_num argument can be omitted. The examples below don't require area_num.

INDEX will return the value that 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.

INDEX MATCH 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 to 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:$B$40,MATCH("Krafty Kids",$C$33:$C$40,0))

And you get the answer; ‘Mr Maker’.

Notice only the Programs column (B) was referenced in INDEX's array argument? This means we can omit INDEX's column number argument as there's only one column in the INDEX array.

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.

Quick Tip : Define Excel Names with 1 Click!

Save time with defined names, but don't waste time creating them one by one! Create a whole table's worth of names with 1 click!

You can also watch this tip on our social media channels:

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub