The Index function and arrays: a question

huskylord

New Member
Joined
Apr 1, 2002
Messages
7
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
"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 :)
 
Upvote 0
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
 
Upvote 0
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}.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
On 2002-04-02 11:33, Aladin Akyurek wrote:
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.


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

Thank you all very much...:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top