# Can INDEX function in Excel return an Array?

Referring to the below screenshot, I have a question about Index Function in MS Excel. Can INDEX actually return an Array? e.g. I have numbers in cells A1:A5 as seen in the screenshot. In Cells C1:C5 I put an Array Formula

``````{=INDEX(A1:A5,ROW(A1:A5))}
``````

and it successfully returns numbers from A1:A5, indicating that INDEX can return an array in a set of cells.

However when I use a formula like this one

``````{=SUM(INDEX(A1:A5,ROW(A1:A5)))}
``````

I was expecting return value of 15 but I get only the first element of the array i.e. 1.

Is this how it is? Can INDEX return an Array that can be used in other Array based formulas?

• You can force INDEX to return an array by using a combination of N and IF functions like this: `=SUM(INDEX(A1:A5,N(IF(1,ROW(A1:A5)))))` - see this question for more: stackoverflow.com/questions/47187863/… – barry houdini Dec 4 '17 at 11:58
• you can use offset and match to return an array, i find it much easier than actually going to the trouble of making an array out of index etc. – PeterH Dec 4 '17 at 13:35

``````=SUM(INDEX(A1:A5,0,1))
``````

With `0` as the row argument, `INDEX` will return the entire column

Since you have only a single column, you can also use:

``````=SUM(INDEX(A1:A5,0))
``````

which, of course, would be equivalent to

``````=SUM(A1:A5)
``````

To expand on @BarryHoudini 's comment, for a non-contiguous array of returns from `INDEX`, you can use something like:

``````=SUM(INDEX(myRange,N(IF(1,{1,3,5}))))
``````

which, in your example array, would return `9`

• also +1 to @barry houdini's comment – rajeev Dec 5 '17 at 10:08