Returning an array from the INDEX function
 

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?

Returning an array from the INDEX function
 

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)

"Agenor" wrote in message
...
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?

Returning an array from the INDEX function
 

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.

"Agenor" wrote in message
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.

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?
....

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})))