Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Can I use INDEX with an ARRAY to return multiple values?

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can I use INDEX with an ARRAY to return multiple values?

    I was trying to grab a range of data using an array as an input to an INDEX (the array in the real formula comes from a MATCH). But the formula I came up with only returns the first hit (Cell A1): answer 1 rather than the sum of rows 1 and 2 as I intended. This is regardless of whether I ENTER or CSE the formula, or even wrap the INDEX in "N()". Is there a way I can grab multiple cells in one INDEX function? I was trying to avoid volatile functions since I have tens of thousands of data points in several tables.

    =SUMPRODUCT(INDEX(A1:C4,{1,2},1))


    Data rows 1-4 of columns A-C:


    1 1 1
    2 2 2
    3 3 3
    4 4 4

  2. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Hi,

    No, you can't use INDEX in this way, unfortunately.

    If you wish to avoid any volatile functions, perhaps:

    =SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:C4),{1,2},0)))*A1:C4)

    Regards

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,394
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Hi Neil

    Is the range you are trying to grab always a contiguous range, like rows 30-40?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    New Member
    Join Date
    Feb 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Thanks XOR - I will stop trying then ! I will give your solution a try, it looks like it could work.

    pgc01 - no it will definitely not be contiguous since I am matching say "HR Total", "IT Total", "Finance Total" which will occur at various points through a large SAP download.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,394
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Quote Originally Posted by neilhousden View Post
    pgc01 - no it will definitely not be contiguous since I am matching say "HR Total", "IT Total", "Finance Total" which will occur at various points through a large SAP download.
    Well, in that case I don't know why you'd use Index(), but, of course, you may be right, as I'm not seeing the whole picture since you did not explain the problem
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    New Member
    Join Date
    Feb 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Quote Originally Posted by pgc01 View Post
    Well, in that case I don't know why you'd use Index(), but, of course, you may be right, as I'm not seeing the whole picture since you did not explain the problem

    Sorry, I obviously summarised the data too much.

    I have a SAP download of many hundreds of lines (it will be different number of lines each month. Like this:

    type
    dept travel sales salary across ten columns
    a 1 1 1
    b 1 1 1
    c 1 1 1
    HR total 3 3 3
    e 2 2 2
    f 2 2 2
    HR project 4 4 4
    g 1 1 1
    IT tot 1 1 1
    h 1 1 1
    IT project 1 1 1
    i 1 1 1
    j 1 1 1
    Fin total 2 2 2
    up to 1,000 rows - and not always the same number



    I would like to summarise the data to total the departments which could consist of several rows (eg IT is IT total & IT project above). I thought an INDEX(MATCHrow, MATCHcolumn) was the best solution given the variability and size of the data. But I am not wedded to any solution.

    Final table something like:

    HR IT Finance
    sales 7 2 2
    salary 7 2 2
    travel 7 2 2

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,394
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Hi

    I made a small change in your result table, to make this with a simple formula.
    The headers in the result table must match the left part of the text in the first column in the main table.

    Since HR and IT already did that I just had to change "Finance" to "Fin".

    In B26:

    =SUMPRODUCT(($B$1:$D$1=$A26)*(LEFT($A$2:$A$15,LEN(B$25))=B$25),$B$2:$D$15)

    Copy down and across



     ABCDE
    1depttravelsalessalary 
    2a111 
    3b111 
    4c111 
    5HR total333 
    6e222 
    7f222 
    8HR project444 
    9g111 
    10IT tot111 
    11h123 
    12IT project123 
    13i111 
    14j111 
    15Fin total222 
    16     
    17     
    18     
    19     
    20     
    21     
    22     
    23     
    24     
    25 HRITFin 
    26sales732 
    27salary742 
    28travel722 
    29     
    [Book1]Sheet6
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    New Member
    Join Date
    Feb 2014
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    Holy spreadsheets. Thank you - I did not realise I could return a 2D array by multiplying a row by a column as you do in the first argument of the SUMPRODUCT. This is a way simpler function.

    Thanks again.
    Neil

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,394
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    You're welcome. Thanks for the feedback.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #10
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I use INDEX with an ARRAY to return multiple values?

    You can try this too:

    Code:
    =SUMPRODUCT(($B$1:$D$1=$L2)*MMULT(--($A$2:$A$15=M$1&{" total"," project"," tot"}),{1;1;1}),$B$2:$D$15)
    Markmzz

Some videos you may like

User Tag List

Tags for this Thread

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
  •