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:
…but if I wrap a SUM around that INDEX and array enter it, I get the wrong answer:
…meaning I have to return that INDEX function to the grid and then point my SUM function at it:
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:
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.
In VBA :
Sub M_snb()msgbox Application.Sum(Application.Index(Array(10, 20, 30, 45, 55), array(2,3)))
Edns sub
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
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
@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…
@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
@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
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
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!
Yeah, he’s a nice guy. :)
Aussie cracks. Man, I could make a meal out of that…
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.
By the way, the link to the post doesn’t work.
Should be:
http://excelxor.com/2014/09/05/index-returning-an-array-of-values/
THanks, Doug…fixed now.
No – my fault that. Think I amended the URL at some point after Jeff posted the original link. Apologies and thanks for correcting.
Regards
Hi Jeff: I want to make you a question about excel. Could you provide me your e-mail?
Regards.