An INDEX of insights from ExcelXOR

Everybody knows that you can’t do some things with the result of an INDEX function unless you return it to the worksheet first. For instance, I can return the 2nd and 3rd members of the array {10,20,30} to the worksheet with an array-entered INDEX function like this:
 

Index1
 
 
…but if I wrap a SUM around that INDEX and array enter it, I get the wrong answer:
 
Index2
 
 
…meaning I have to return that INDEX function to the grid and then point my SUM function at it:
 
Index3
 
 
Everybody knows that, right? Well, everybody’s wrong. Everybody but the author over at the new ExcelXOR blog, that is. At this earth-shattering post the Author shows several intriguing ways to get INDEX to play nicely with outer functions:
 

Index4
 
 

Far out! That’s the kind of trick I would have sold my soul to the devil to learn.

There’s a lot to learn at that site: go to http://excelxor.com/blog/ and scroll most of the way down until the Archives section, and then get clicking. The earliest post is practically from yesterday: August 2014. But there’s a lifetime of learnings there already…both in the posts, and in the comments. Plus the most wicked formula challenges you will find in one place.

16 thoughts on “An INDEX of insights from ExcelXOR

  1. OMG……I don’t believe this….The site is a goldmine of Info… but this tip alone changes so many things for me. Thanks Jeff for this post and for the link

  2. Greetings, fellow Excellers!

    Actually I should mention that I was not the discoverer of this technique, though it’s surprising how well-hidden it’s remained for so long.

    I first came across it in a random post by a certain “wcymiss” over at ExcelForum. Sadly their visit was all too brief, though I would strongly recommend having a look at some of the posts he/she made in that short time: http://www.excelforum.com/members/737946.html. Some truly astonishing and humbling stuff (including this N(IF(1, technique for coercing INDEX which I elaborate upon).

    Thanks for the “plugs”! And keep up the good work!

    Cheers

  3. @snb: Interesting.
    @sam: Thought you’d like it. That tip alone is like a mathematician suddenly discovering that 5 isn’t a prime after all.
    @ XOR LX: Thanks for the link. There is an awful lot of gold in help forums. But finding it in old posts is like sifting through the tailings of an abandoned mine…

  4. @Jeff,
    I love the fact that this can return discontinuous “usable” ranges from a Rectangular range
    If d = “A1:D10” then
    =INDEX(d,N(IF(1,ROW(d))),N(IF(1,{2,4})))
    returns B1:B10,D1:D10 – Brilliant.
    XOR LX / wcymiss – Thanks

  5. @sam

    Indeed! One of the most important practical uses I’ve found for this property of INDEX is that it can be used to “re-dimension” arrays “in-formula”, thus allowing further manipulation of that array.

    This gives us the ability to form constructions which would otherwise be invalid due to their requiring a third, or fourth “dimension”.

    One such example is here:

    http://excelxor.com/2014/11/08/unique-alphabetical-list-from-several-columns/

    where a standard construction for extracting unique values which employs certain functions valid only for one-dimensional ranges is extended to work on two-dimensional ranges by first “re-dimensioning” that range into one of just a single dimension using INDEX in this way.

    And this is just one example: there are many cases in which we want to return more than a single value from a function-generated array, but of course OFFSET, for example, will not accept anything other than an actual worksheet reference for its first parameter. With this INDEX technique we now have a means by which we can achieve the equivalent results.

    Cheers

  6. To make this easier to edit try the following.

    J1={2,4}

    Create the below range name:
    rCol=EVALUATE($J$1)

    Change formula to
    =INDEX(d,N(IF(1,ROW(d))),N(IF(1,rCol)))

    Regards

  7. Thanks Jeff, this is really an amazing post amongst so many from you over the last year. It’s the sort of post that makes me even forgive some of your Aussie cracks. It’s good to see you allowing Dick a few guest posts on your site lately too!

  8. Jeff – I’m sure you could, just don’t expect us to eat it, OK?

    But seriously, I missed this post the first time, or more likely read it then promptly forgot about it over Christmas (blame Christmas, nothing to do with age).

    So thanks for the tip and the link.

    Unlike Aussie cracks, you can never have too many good active Excel blogs.

  9. No – my fault that. Think I amended the URL at some point after Jeff posted the original link. Apologies and thanks for correcting.

    Regards

Leave a Reply

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