Can I use INDEX with an ARRAY to return multiple values?

[neilhousden]

I was trying to grab a range of data using an array as an input to an INDEX (the array in the real formula comes from a MATCH). But the formula I came up with only returns the first hit (Cell A1): answer 1 rather than the sum of rows 1 and 2 as I intended. This is regardless of whether I ENTER or CSE the formula, or even wrap the INDEX in "N()". Is there a way I can grab multiple cells in one INDEX function? I was trying to avoid volatile functions since I have tens of thousands of data points in several tables.

=SUMPRODUCT(INDEX(A1:C4,{1,2},1))


Data rows 1-4 of columns A-C:

1</SPAN>	1</SPAN>	1</SPAN>
2</SPAN>	2</SPAN>	2</SPAN>
3</SPAN>	3</SPAN>	3</SPAN>
4</SPAN>	4</SPAN>	4</SPAN>

<TBODY>
</TBODY>

 

[neilhousden]

Thanks markmzz. And that formula may lend itself to more "automation" of the arrays using MATCH etc.

Very useful addition. Cheers,
neil

 

[markmzz]

Thanks markmzz. And that formula may lend itself to more "automation" of the arrays using MATCH etc.

Very useful addition. Cheers,
neil

You are welcome and thanks for the feedback.

Markmzz

 

[XOR LX]

No, you can't use INDEX in this way, unfortunately.

For the record, and in case people come across this thread via search engines, I have since discovered that this is in fact not true:

INDEX: Returning an array of values | EXCELXOR

Regards

 

[Aladin Akyurek]

I was trying to grab a range of data using an array as an input to an INDEX (the array in the real formula comes from a MATCH). But the formula I came up with only returns the first hit (Cell A1): answer 1 rather than the sum of rows 1 and 2 as I intended. This is regardless of whether I ENTER or CSE the formula, or even wrap the INDEX in "N()". Is there a way I can grab multiple cells in one INDEX function? I was trying to avoid volatile functions since I have tens of thousands of data points in several tables.

=SUMPRODUCT(INDEX(A1:C4,{1,2},1))


Data rows 1-4 of columns A-C:

1</SPAN>	1</SPAN>	1</SPAN>
2</SPAN>	2</SPAN>	2</SPAN>
3</SPAN>	3</SPAN>	3</SPAN>
4</SPAN>	4</SPAN>	4</SPAN>

<TBODY>
</TBODY>

=SUM(OFFSET($A$1,0,{0,1},2,3))

would be a somewhat obvious set up.

Using INDEX instead of OFFSET requires specifying rows 1 and 2 plus all columns of A1:C2. That is:

SUM of INDEX(A1:C4,1,0) + SUM of INDEX(A1:C4,2,0)

[1] The following won't work:

SUM(INDEX(A1:C4,{1;2},{0,0}))

for the formula cannot interpret {1;2} as rows 1 and 2 and {0,0} as all cells of the rows in question.

A possible way is to use a function for dereferencing or a function for a second round of evaluation. [ The OP appears to be aware of N which can effect the required dereferencing (other functions that are used for the same purpose are SUBTOTAL and TRANSPOSE). ]

Composing an array constant is often done with CHOOSE

CHOOSE(1,{1;2})

as with IF (I saw at least once someone using it)...

IF(1,{1;2})

However, the following won't still not succeed as intended:

SUM(INDEX(A1:C4,CHOOSE(1,{1;2}),CHOOSE(1,{0,0})))

unless we add a dereferencer:

SUM(INDEX(A1:C4,N(CHOOSE(1,{1;2})),N(CHOOSE(1,{0,0}))))

Confirmed with control+shift+enter, not just enter, this formula will achieve the intended sum. Substituting IF for CHOOSE, we would have:

=SUM(INDEX(A1:C4,N(IF(1,{1;2})),N(IF(1,{0,0}))))

also to be confirmed with control+shift+enter.

Keywords for searching with Google, for example: dereferencing (also "deferencing", a typo I committed at least once), second round of evaluation, CHOOSE (also interesting to see how CHOOSE is used to construct a reference involving non-contiguous cells).

 

[XOR LX]

=SUM(INDEX(A1:C4,N(IF(1,{1;2})),N(IF(1,{0,0}))))

=SUM(INDEX(A1:C4,N(CHOOSE(1,{1;2})),N(CHOOSE(1,{0,0}))))

Neither of those formulas sums the first two rows in A1:C4. They are both equivalent to

=2*(A1+A2)

The correct technique I give here:

INDEX: Returning an array of values | EXCELXOR

And you don't need to use an array formula.

A construction such as:

=SUM(INDEX(A1:C4,N(IF(1,{1;2})),N(IF(1,{1,2,3}))))

will sum the first two rows in A1:C4 and does not need to be committed with CSE.

Regards

 

[Aladin Akyurek]

Try...

=SUM(INDEX(A1:C4,N(CHOOSE(1,{1;2})),N(CHOOSE(1,{1,2,3}))))

The implicated claim still holds...

 

[XOR LX]

Try...

=SUM(INDEX(A1:C4,N(CHOOSE(1,{1;2})),N(CHOOSE(1,{1,2,3}))))

The implicated claim still holds...

What is the "implicated claim"?

Regards

 

[neilhousden]

Thanks. I am still following the thread and this will be a useful addition to my arsenal.

 

[XOR LX]

Thanks. I am still following the thread and this will be a useful addition to my arsenal.

Good to hear!

Cheers

 

[Aladin Akyurek]

Thanks. I am still following the thread and this will be a useful addition to my arsenal.

The most generic set up that you can have is:

=SUM(OFFSET($A$1,0,{0,1},2,3))

followed by either:

=SUM(INDEX(A1:C4,N(CHOOSE(1,{1;2})),N(CHOOSE(1,{1,2,3}))))

or:

=SUM(INDEX(A1:C4,N(IF(1,{1;2})),N(IF(1,{1,2,3}))))

For more generality, the array constants must be replaced with appropriate expressions that generates them, i.e., expressions with ROW/INDIRECT, all volatile.