1

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?

enter image description here

  • 1
    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
1
=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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.