Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: The Index function and arrays: a question

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the MID function:

    =MID(C5,3,1)

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    "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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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.


  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •