How to use INDEX() inside ARRAYFORMULA()?

Question

I am trying to use the INDEX() formula inside an ARRAYFORMULA(). As a simple (non-sense) example, with 4 elements in column A, I expected that the following array formula entered in B1 would display all four elements from A in column B:

=ARRAYFORMULA(INDEX($A$1:$A$4,ROW($A$1:$A$4)))

However, this only fills field B1 with a the value found in A1.

When I enter

=ARRAYFORMULA(ROW($A$1:$A$4))

in B1, then I do see all numbers 1 to 4 appear in column B. Why does my first array formula not expand similar like the second one does?

Answer 1

The INDEX function is one that does not support "iteration" over an array if an array is used as one of its arguments. There is no documentation of this that I know of; it simply is what it is. So the second argument will always default to the first element of the array, which is ROW(A1).

One clumsy workaround to achieve what you require relies on a second adjacent column existing next to the source data* (although it is unimportant what values are actually in that second column):

=ArrayFormula(HLOOKUP(IF(ROW($A$1:$A$4);$A$1);$A$1:$B$4;ROW($A$1:$A$4);0))

or indeed something like:

=ArrayFormula(HLOOKUP(IF({3;2;4;1};$A$1);$A$1:$B$4;{3;2;4;1};0))

---

edit 2015-06-09

* This is no longer a requirement in the newest version of Sheets; the second argument in the HLOOKUP can just be $A$1:$A$4.