How to use INDEX() inside ARRAYFORMULA()?
google-sheets
Solution
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.
Problem
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?