Thanks:  0
Likes:  0

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

1. ## 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. ## 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. ## 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?

4. ## 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. ## Re: Can I use INDEX with an ARRAY to return multiple values?

Originally Posted by neilhousden
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

6. ## Re: Can I use INDEX with an ARRAY to return multiple values?

Originally Posted by pgc01
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. ## 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

8. ## 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. ## Re: Can I use INDEX with an ARRAY to return multiple values?

You're welcome. Thanks for the feedback.

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