The Index function and arrays: a question

[huskylord]

Hello to all,

First time poster here so I hope I don't inadverdantly break any taboos around here.

My question involves a simple arrary. Let's say that cell C5 has the arrary {5,6,8,4}.
Now lets say that I am on cell H5 and I need to extract the 2nd value from the known arrary on C5. I believe that the Index function is what's necessary. According to the Index function, 3 arguments are required, the array, the row number and the column number. Is it possible to specify a cell value that has a current array within as the array arguement for the Index function? Am i completely approaching this wrong? Please let me know if i've confused everyone here as much myself.

thank you


[ This Message was edited by: huskylord on 2002-04-02 07:52 ]

[Al Chara]

Try the MID function:

=MID(C5,3,1)

[Mark W.]

Does C5 contain the text string, "{5,6,8,4}", or the formula (array constant), ={5,6,8,4}?

BTW, =INDEX({5,6,8,4},1,2), returns 6.

[ This Message was edited by: Mark W. on 2002-04-02 08:25 ]

[huskylord]

"BTW, =INDEX({5,6,8,4},1,2), returns 6."

Thank you for the responses. I've tried the above statement, but that requires the whole array to be included within the formula. Tell me if i'm just looking for the slacker solution, but I was hoping I could take the same array, place it in any particular cell, (example C5, and from a different cell location, identify a particular number within the array.
In essence, is this possible =>
Index(C5,1,2) where C5 is an array.

Once again, thank you

[Mark W.]

In essence, is this possible =>
Index(C5,1,2) where C5 is an array.

Not if C5 contains "{5,6,8,4}". Even if C5 contained the array constant, ={5,6,8,4}, =INDEX(C5,1,2), wouldn't return 6 because a single cell can only hold one value of the array -- in this case the 1st value -- 5.

In fact, =INDEX(C5,1,2), would return a #REF! error since as a single cell C5 doesn't have 2 columns.

[ This Message was edited by: Mark W. on 2002-04-02 09:17 ]

[Mark W.]

You could use =INDEX(My_Array,1,2) to return 6 where My_Array is a defined name that refers to ={5,6,8,4}.

[huskylord]

Just tried out your suggestion and the only valid number that comes back is the for the first value in the array...any other reference comes back as an error.

[ This Message was edited by: huskylord on 2002-04-02 10:36 ]

[Albert 1]

=CHOOSE(A1,MID(C5,1,1),MID(C5,3,1),MID(C5,5,1),MID(C5,7,1))
(A1=1, 2, 3, or 4)

[Aladin Akyurek]

On 2002-04-02 10:35, huskylord wrote:
Just tried out your suggestion and the only valid number that comes back is the for the first value in the array...any other reference comes back as an error.

[ This Message was edited by: huskylord on 2002-04-02 10:36 ]

A defined name requires that you

activate the option Insert|Name|Define,
enter My_Array as name in the Names in Workbook box,
and enter as formula in the Refers to box:

={5,6,8,4}

INDEX can then be applied to My_Array, as Mark suggested.

[huskylord]

On 2002-04-02 11:33, Aladin Akyurek wrote:

A defined name requires that you

activate the option Insert|Name|Define,
enter My_Array as name in the Names in Workbook box,
and enter as formula in the Refers to box:

={5,6,8,4}

INDEX can then be applied to My_Array, as Mark suggested.

Tried it and...it works!!!!

Thank you all very much...