Using INDEX/MATCH as a Cell Reference

Last week during our adventure in Omaha, Dick Kusleika taught me two things.

First, he introduced me to a new beer: Lucky Bucket Certified Evil. It’s a dark Belgian strong ale brewed right in Kusleika’s hometown of Omaha Nebraska. Apparently, it won Silver in the World Beer Championships in 2009 (I didn’t even know there was such a competition). I’m no beer expert, but I’d say that this is definitely one of the top 10 beers I’ve had.

.

.

The other (Excel related) thing Kusleika taught me in Omaha: you can use INDEX/MATCH as a cell reference. I’m not sure how I’ve gone this long without knowing this trick, but I’m happy to share it with you today.

.

Let’s say that I have a range of data and I want to get the average of January thru July. I can of course simply reference that range as shown here.

.

But what if I wanted to make the average calculation dynamic?

That is to say, what if I wanted to dynamically change the thru month from July to some other month? Well, as it turns out, I can replace my hard-coded cell reference with an INDEX/MATCH formula.

.

How does it work? Frankly, it’s still kind of a mystery to me, but here’s the gist.

If I enter just an INDEX/MATCH formula, Excel returns the appropriate value for my month (July’s 149 in this case).

.

But if I take the same INDEX/MATCH formula and use it as a cell reference, Excel understands what I’m doing and uses the INDEX/MATCH as a cell address.

Who knew?

.

With this formula, I can change my month to May, and get the average of January thru May.

.

It’s amazing that someone as handsome and accomplished as I am would be the last of the Excel guys to know about this..

I’m curious how some of you use this technique (assuming you already knew this).

11 thoughts on “Using INDEX/MATCH as a Cell Reference

  1. John

    I believe index returns a reference to the cell (ie cell C9) and not the actual value. Likewise you can use Offset as well.

    =AVERAGE(C3:OFFSET(C2,MATCH(F2,B3:B14,0),0))

    There’s probably a few others which allow this sort of thing.

  2. Andy Pope

    Used as an alternative to OFFSET when creating dynamic named ranges for charts

    =Sheet1!$C$3:$C$3:INDEX(Sheet1!$C$3:$C$14,MATCH(Sheet1!$F$2,Sheet1!$B$3:$B$14,0),1)

  3. Leo

    I use it a lot with dashboard selectors. I learnt this technique from ExcelHero in his http://www.excelhero.com/blog/2011/03/the-imposing-index.html.
    That article is “must read” one for anyone who is serious about Excel.
    Once in a while I read it again(both the article and comments) and often find new applications for what is explained there.
    The cool part is that we can have INDEX on either side( or even on both sides) of the reference operator “:”.
    In your example you can make a sliding adjustable window of selected months.
    And we are not limited to INDEX or OFFSET: it can be any expression that returns a valid cell reference.

  4. Khushnood Viccaji

    Using the same logic, we can also keep the starting month as a variable.
    That is, show the average for a ‘variable period’ from say, Feb to Jul (6 mths), or say Apr to Dec (9 mths).

    =AVERAGE( INDEX(C3:C14,MATCH(F1,B3:B14,0)) : INDEX(C3:C14,MATCH(F2,B3:B14,0)) )

    The start-month is in cell F1 and end-month is in F2.

  5. Jordan G

    I actually first learned about this from Daniel Ferry’s Imposing Index article, which Leo has posted above.

    One trick I’ve found one to “prove” it’s pulling back a reference instead of a value is to wrap the Index function in the Cell function – and have it return the address. For example, If you do something like this…

    =CELL(“address”, INDEX(…MATCH()…)

    CELL will, in fact, return the cell address given by result of your INDEX/MATCH function – even though INDEX/MATCH appears to pass a value into that second parameter.

  6. KMH

    THANK YOU! I’ve been trying to find a way to quickly calculate standard deviation across various times and indices. This helped so much!

  7. Stuart

    is there a way to look for a cell value just like the “Find” tool from the Menu bar? but as a cell formula/function instead?

Leave a Reply

Your email address will not be published. Required fields are marked *