Some Unique Uses of INDEX and MATCH Functions In Excel

In the past we’ve used the combination of INDEX and MATCH to extract the value at the intersection of a row and column of a data range. But there is more that you can do with this function combination, and we’ll look at those options in this tutorial.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have a typical example of the use of the INDEX/MATCH combination. We have a data range along with Data Validation drop down lists for Quarter and Territory:

The formula we use in cell J4 is:

=INDEX(B2:G5,MATCH(J2,A2:A5,0),MATCH(J3,B1:G1,0))

If you are not familiar with using this function combination, you can find more information here and here.

The syntax for this use of the INDEX function is:

=INDEX(array,row_num,[col_num])

But there is another syntax for the INDEX function called the Reference form, and that is:

=INDEX(reference,row_num,[col_num],[area_num])

Here is an example of the use of this syntax. We have four different data arrays:

We also have data validation drop down lists for Quarter, Territory, but also for Region:

Adding that third dimension of region changes the structure of the formula. The one we have in cell J6 is:

=INDEX((B2:G5,B8:G11,B14:G17,B20:G23),MATCH(J3,A2:A5,0),MATCH(J4,B1:G1,0),K5)

The breakdown of this formula based on the syntax is as follows:

Reference: (B2:G5,B8:G11,B14:G17,B20:G23)

Row number: MATCH(J3,A2:A5,0)

Column number: MATCH(J4,B1:G1,0)

Area number: K5

The row number and column number are typical uses of the MATCH functions to indicate those. The keys here are the arguments of Reference and Area number. Note the reference indicates the four different data ranges for each of the North, South, East and West regions. These MUST be on the same worksheet and need to be within a set of parentheses. Then the Area number indicates which region to use for the INDEX function. In cell K5, I have the following formula:

=IF(J5=”North”,1,IF(J5=”South”,2,IF(J5=”East”,3,4)))

So based on the choice of regions, it will indicate to use the first, second, third, or fourth data range for the INDEX function to choose from.

Watch the video for more details, AND for two more unique ways to use the INDEX/MATCH combination for pulling in ranges of data!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

One Response to Some Unique Uses of INDEX and MATCH Functions In Excel

  1. Peter Bartholomew September 18, 2017 at 2:51 pm #

    A pretty thorough workout of the index function.

    I don’t know how you cope with direct cell referencing though. To me
    (B2:G5,B8:G11,B14:G17,B20:G23)
    looks horrible and I would far prefer to see the range union described by name, “SalesByRegion” (say).
    The formulas such as
    =INDEX(SalesByRegion,0,MATCH(TerritorySelected,TerritoryNames,0),RegionIndex)
    then looks intelligible.

    Perhaps in a blog, you have to use direct cell references to be understood. I just wish they had never been invented.

Leave a Reply