INDEX function (array type)

[Sean]

Maybe I'm missing a trick here.

The formula =INDEX({1,2,3},1,2) will return the value 2

The formula =INDEX(A1,1,2) where the cell A1 contains the array
constant ={1,2,3} will return a #REF! error

Why does this happen and what is wrong with referencing a array in a
cell?

 

[Sean Timmons]

you are referencing values rather than hard-coding them. In this case, the
values are treated as text rather than an array. There's usually an easier
way around this issue if you're trying to calculate something...

 

[Shane Devenshire]

Hi,

the arguments 1 and 2 in both examples refers to the first row, second
column. Now the second column when referencing a spreadsheet cell is one
column to the right of the reference, here with A1:B1 as the reference, B1 is
the second column. Excel does not look inside of the cell and take the
second entry.

There is also a second issue, since A1 is a one column reference asking for
the second column is asking for a column outside the reference range, again
that is column B

 

[Sean]

I understand what you're saying Shane, however I an trying to use the
Array form of the INDEX() function and not the Reference form.


Sean

 

[Sean]

Yes, there probably is an easier way around this but that doesn't
solve my primary dilemma.

If (as you say )the Array constant in cell A1 is being treated as text
instead of an array, is there a way to force it to be treated as such.
Something like what the INDIRECT() function does for address strings?

S.