Using INDEX with non-contiguous ranges

[HighAndWilder]

I'd like to be able to use INDEX with non-contiguous ranges.

In this example I would like the formula to return 17 but it returns 10.

Maybe the question should be, how can I return 17 from the two non-contiguous ranges when I know the row and column numbers.

The numbers will not always be unique.

I have not come across this kind of range reference before.

Thanks for any pointers in the right direction.

Version 2.xlsm
	A	B	C	D	E	F	G	H	I
1
2		1	2	3	4	5	6	7
3		8	9	10	11	12	13	14
4		15	16	17	18	19	20	21
5
6
7		22	23	24	25	26	27	28
8
9		29	30	31	32	33	34	35
10
11		10
12
NCR

Cell Formulas
Range	Formula
B11	B11	=INDEX($B$2:$H$2:$B$4:$H$4:$B$7:$H$7:$B$9:$H$9,2,3)

 

[hagia_sofia]

Hello, you could e.g. use CHOOSEROWS:

=INDEX(CHOOSEROWS(B2:H9,1,3,6,8),2,3)

 

[Peter_SSs]

.. or a bit more like the syntax that you were trying

=INDEX(VSTACK(B2:H2,B4:H4,B7:H7,B9:H9),2,3)

 

[Peter_SSs]

Another option directly with index
INDEX(reference, row_num, column_num, area_num)
So ..

=INDEX((B2:H2,B4:H4,B7:H7,B9:H9),1,3,2)

 

[Cubist]

Another option where:
row index= 2
col index = 3
number of col of dataset= 7
Note: this won't work if you have blanks in your data rows.

Book1
	A	B	C	D	E	F	G	H
1
2		1	2	3	4	5	6	7
3		8	9	10	11	12	13	14
4		15	16	17	18	19	20	21
5
6
7		22	23	24	25	26	27	28
8
9		29	30	31	32	33	34	35
10
11		10
Sheet3

Cell Formulas
Range	Formula
B11	B11	=INDEX(TOCOL(B2:H9,1),(2-1)*7+3)

 

[Cubist]

A more robust way

=INDEX(FILTER(B2:H9,BYROW(B2:H9<>"",OR)),2,3)

 

[Peter_SSs]

Another option

A more robust way

@Cubist
I think that you may have mis-read the question with both of your suggestions.

In this example I would like the formula to return 17 but it returns 10.

Maybe the question should be, how can I return 17

 

[Cubist]

@Cubist
I think that you may have mis-read the question with both of your suggestions.

Yes, simply change 2 to 3 in both.

 

[Peter_SSs]

Yes, simply change 2 to 3 in both.

That does not seem logical or very user-friendly to me. For both of your formulas then to get the value from the third column of ..

• the 4th range you would use 5
• the 3rd range you would use 4
• the 2nd range you would use 3
  but for
• the 1st range you would have to use 1

 

[Cubist]

I'm not following what you mean.

Book1
	B	C	D	E	F	G	H
1
2	1	2	3	4	5	6	7
3	8	9	10	11	12	13	14
4	15	16	17	18	19	20	21
5
6
7	22	23	24	25	26	27	28
8
9	29	30	31	32	33	34	35
10
11	Row	Col	Option1	Option2
12	1	1	1	1
13	1	2	2	2
14	1	3	3	3
15	1	4	4	4
16	1	5	5	5
17	1	6	6	6
18	1	7	7	7
19	2	1	8	8
20	2	2	9	9
21	2	3	10	10
22	2	4	11	11
23	2	5	12	12
24	2	6	13	13
25	2	7	14	14
26	3	1	15	15
27	3	2	16	16
28	3	3	17	17
29	3	4	18	18
30	3	5	19	19
31	3	6	20	20
32	3	7	21	21
33	4	1	22	22
34	4	2	23	23
35	4	3	24	24
36	4	4	25	25
37	4	5	26	26
38	4	6	27	27
39	4	7	28	28
40	5	1	29	29
41	5	2	30	30
42	5	3	31	31
43	5	4	32	32
44	5	5	33	33
45	5	6	34	34
46	5	7	35	35
Sheet3

Cell Formulas
Range	Formula
B12:B46	B12	=ROUNDUP(SEQUENCE(35)/7,0)
D12:D46	D12	=INDEX(TOCOL($B$2:$H$9,1),(B12-1)*7+C12)
E12:E46	E12	=INDEX(FILTER($B$2:$H$9,BYROW($B$2:$H$9<>"",OR)),B12,C12)
Dynamic array formulas.