Can Excel's INDEX function return array?

Question

If the data in the range A1:A4 is as follows:

Apple
Banana
Orange
Strawberry

Then INDEX can be used to individually return any value from that list, e.g.

= INDEX(A1:A4,3)

Would return Orange.

Is there a similar Excel functions or combination of functions that would effectively allow you to do something like this:

= INDEX(A1:A4,{2;3})

Which would return an array {Banana;Orange}?

Is this possible (preferably without VBA), and if so, how? I'm having a tough time figuring out how to accomplish this, even with the use of helper cells.

I can figure out a somewhat complicated solution if the data is numbers (using MMULT), but the fact that the data is text is tripping me up because MMULT does not work with text.

Answer 1

OFFSET is probably the function you want.

=OFFSET(A1:A4,1,,2)

But to answer your question, INDEX can indeed be used to return an array. Or rather, two INDEX functions with a colon between them:

=INDEX(A1:A4,2):INDEX(A1:A4,3)

This is because INDEX actually returns a cell reference OR a number, and Excel determines which of these you want depending on the context in which you are asking. If you put a colon in the middle of two INDEX functions, Excel says "Hey a colon...normally there is a cell reference on each side of one of these" and so interprets the INDEX as just that. You can read more on this at http://blog.excelhero.com/2011/03/21/the_imposing_index/

I actually prefer INDEX to OFFSET because OFFSET is volatile, meaning it constantly recalculates at the drop of a hat, and then forces any formulas downstream of it to do the same. For more on this, read my post https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

You can actually use just one INDEX and return an array, but it's complicated, and requires something called dereferencing. Here's some content from a book I'm writing on this:

The worksheet in this screenshot has a named range called Data assigned to the range A2:E2 across the top. That range contains the numbers 10, 20, 30, 40, and 50. And it also has a named range called Elements assigned to the range A5:B5. That Elements range tells the formula in A8:B8 which of those five numbers from the Data range to display.

If you look at the formula in A8:B8, you’ll see that it’s an array-entered INDEX function: {=INDEX(Data,Elements)}. This formula says, “Go to the data range and fetch elements from it based on whatever elements the user has chosen in the Elements range.” In this particular case, the user has requested the fifth and second items from it. And sure enough, that’s just what INDEX fetches into cells A8:B8: the corresponding values of 50 and 20.

But look at what happens if you take that perfectly good INDEX function and try to put a SUM around it, as shown in A11. You get an incorrect result: 50+20 does not equal 50. What happened to 20, Excel?

For some reason, while =INDEX(Data,Elements) will quite happily fetch disparate elements from somewhere and then return those numbers separately to a range, it is rather reluctant to comply if you ask it to instead give those numbers to another function. It’s so reluctant, in fact, that it passes only the first element to the function.

Consequently, you’re seemingly forced to return the results of the =INDEX(Data,Elements) function to the grid first if you want to do something else with it. Tedious. But pretty much every Excel pro would simply tell you that there’s no workaround...that’s just the way it is, and you have no other choice.

Buuuuuuuut, they’re wrong. At the post http://excelxor.com/2014/09/05/index-returning-an-array-of-values/, mysterious formula superhero XOR outlines two fairly simple ways to “de-reference” INDEX so that you can then use its results directly in other formulas; one of those methods is shown in A18 above. It turns out that if you amend the INDEX function slightly by adding an extra bit to encase that Elements argument, INDEX plays ball. And all you need to do is encase that Elements argument as I've done below:

N(IF({1},Elements))

With this in mind, your original misbehaving formula:

=SUM(INDEX(Data,Elements))

...becomes this complex but well-mannered darling:

=SUM(INDEX(Data, N(IF({1},Elements))))

Answer 2

You can get this type of behavior without an array formula. In say D1

=IFERROR(INDEX($A$1:$A$4,CHOOSE(ROWS($1:1),2,3)),"")

and copy down. Note the 2,3 is buried inside the CHOOSE() function.

You can replace the 2,3 with any set of indices.