Returning an array from the INDEX function

[Guest]

I want to return a non-contiguous subset of another array using the INDEX
function. For example:

=INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})

When selecting a 2x2 array of cells and hitting CTRL + SHIFT + ENTER, the
following array is returned:

[2 3
8 9]

However when using the formula "INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})" as
an input to another function (within the actual formula) it seems only the
top-left element is input i.e. (2).

For example:
ROWS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3}))

returns 1.

As does,
COLUMNS(INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3}))

Does anyone have any idea how to allow Excel to actually pass the entire
array into the function rather than just the first element?

 

[Bob Phillips]

Isn't it just the function you are using, trying to get the row of a number
doesn't make sense.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Harlan Grove]

Bob Phillips wrote...

Isn't it just the function you are using, trying to get the row of a number
doesn't make sense.

If the OP were calling ROW or COLUMN, your comment might apply. However
the OP is calling ROWS and COLUMNS, functions which *both* operate on
constant and derived arrays, so looks like your comment is irrelevant.

I want to return a non-contiguous subset of another array using the INDEX
function. For example:

=INDEX({1,2,3;4,5,6;7,8,9},{1;3},{2,3})

When selecting a 2x2 array of cells and hitting CTRL + SHIFT + ENTER, the
following array is returned:

[2 3
8 9]

This is an example of Excel's implicit indexing. It's basically an
undocumented 'feature'.

When you pass arrays as 2nd or 3rd arguments to INDEX, it returns an
object that works like a range reference when it comes to implicit
indexing. That return object is something Excel can load into each
selected cell as a separate result, but it's not something Excel can
use as an expression in longer formulas. In the latter case, Excel will
convert INDEX's return value into just the first item in the
pseudoarray result.
....

The only way to do this without VBA requires some basic linear algebra.
For example,

=MMULT({1,0,0;0,0,1},MMULT({1,2,3;4,5,6;7,8,9},{0,0;1,0;0,1}))

returns {2,3;8,9}, and the formula

=ROWS(MMULT({1,0,0;0,0,1},MMULT({1,2,3;4,5,6;7,8,9},{0,0;1,0;0,1})))

returns 2. If you want to generalize this, define the name A referring
to ={1,2,3;4,5,6;7,8,9}, then try

=MMULT(--(COLUMN($A$1:INDEX($1:$1,COLUMNS(A)))={1;3}),
MMULT(A,--(ROW($A$1:INDEX($A:$A,ROWS(A)))={2,3})))