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)

 

[Peter_SSs]

I'm not following what you mean.

Unless I (& @hagia_sofia) are mistaken, if you look at the OP's formula in post 1, B3:H3 is not part of any range of interest. The formula shows 4 ranges, as does the blue shading.

 

[Cubist]

I've missed that detail. Can add an additional condition to filter out that row.

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	Return
12	1	1	1
13	1	2	2
14	1	3	3
15	1	4	4
16	1	5	5
17	1	6	6
18	1	7	7
19	2	1	15
20	2	2	16
21	2	3	17
22	2	4	18
23	2	5	19
24	2	6	20
25	2	7	21
26	3	1	22
27	3	2	23
28	3	3	24
29	3	4	25
30	3	5	26
31	3	6	27
32	3	7	28
33	4	1	29
34	4	2	30
35	4	3	31
36	4	4	32
37	4	5	33
38	4	6	34
39	4	7	35
Sheet3

Cell Formulas
Range	Formula
B12:B39	B12	=ROUNDUP(SEQUENCE(28)/7,0)
D12:D39	D12	=INDEX(FILTER($B$2:$H$9,BYROW($B$2:$H$9<>"",OR)*(SEQUENCE(ROWS($B$2:$H$9))<>2)),B12,C12)
Dynamic array formulas.

 

[Peter_SSs]

Can add an additional condition to filter out that row.

It does depend a bit on how the OP want to refer to the different ranges (would be good to get some feedback @HighAndWilder as I have noticed you on the forum a few times since suggestions have been made ) but I see that method as not very flexible. For example in the sheet below, I have your formulas in column D and the formulas from posts 3 and 4 (adapted to your results layout) in E & F.

24 12 15.xlsm
	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	Return	Post 3	Post 4
12	1	1	1	1	1
13	1	2	2	2	2
14	1	3	3	3	3
15	1	4	4	4	4
16	1	5	5	5	5
17	1	6	6	6	6
18	1	7	7	7	7
19	2	1	15	15	15
20	2	2	16	16	16
21	2	3	17	17	17
22	2	4	18	18	18
23	2	5	19	19	19
24	2	6	20	20	20
25	2	7	21	21	21
26	3	1	22	22	22
27	3	2	23	23	23
28	3	3	24	24	24
29	3	4	25	25	25
30	3	5	26	26	26
31	3	6	27	27	27
32	3	7	28	28	28
33	4	1	29	29	29
34	4	2	30	30	30
35	4	3	31	31	31
36	4	4	32	32	32
37	4	5	33	33	33
38	4	6	34	34	34
39	4	7	35	35	35
Disjoint ranges (3)

Cell Formulas
Range	Formula
D12:D39	D12	=INDEX(FILTER($B$2:$H$9,BYROW($B$2:$H$9<>"",OR)*(SEQUENCE(ROWS($B$2:$H$9))<>2)),B12,C12)
E12:E39	E12	=INDEX(VSTACK(B$2:H$2,B$4:H$4,B$7:H$7,B$9:H$9),B12,C12)
F12:F39	F12	=INDEX((B$2:H$2,B$4:H$4,B$7:H$7,B$9:H$9),1,C12,B12)

That's fine, but not only are the earlier ones considerably shorter, they also automatically adapt if further rows are inserted for example whereas your may require a physical change to the formula, depending on where inserted.

24 12 15.xlsm
	B	C	D	E	F	G	H
1
2	1	2	3	4	5	6	7
3
4	8	9	10	11	12	13	14
5	15	16	17	18	19	20	21
6
7
8	22	23	24	25	26	27	28
9
10	29	30	31	32	33	34	35
11
12	Row	Col	Return	Post 3	Post 4
13	1	1	1	1	1
14	1	2	2	2	2
15	1	3	3	3	3
16	1	4	4	4	4
17	1	5	5	5	5
18	1	6	6	6	6
19	1	7	7	7	7
20	2	1	8	15	15
21	2	2	9	16	16
22	2	3	10	17	17
23	2	4	11	18	18
24	2	5	12	19	19
25	2	6	13	20	20
26	2	7	14	21	21
27	3	1	15	22	22
28	3	2	16	23	23
29	3	3	17	24	24
30	3	4	18	25	25
31	3	5	19	26	26
32	3	6	20	27	27
33	3	7	21	28	28
34	4	1	22	29	29
35	4	2	23	30	30
36	4	3	24	31	31
37	4	4	25	32	32
38	4	5	26	33	33
39	4	6	27	34	34
40	4	7	28	35	35
Disjoint ranges (3)

Cell Formulas
Range	Formula
D13:D40	D13	=INDEX(FILTER($B$2:$H$10,BYROW($B$2:$H$10<>"",OR)*(SEQUENCE(ROWS($B$2:$H$10))<>2)),B13,C13)
E13:E40	E13	=INDEX(VSTACK(B$2:H$2,B$5:H$5,B$8:H$8,B$10:H$10),B13,C13)
F13:F40	F13	=INDEX((B$2:H$2,B$5:H$5,B$8:H$8,B$10:H$10),1,C13,B13)

It may not be at all relevant to this thread but another advantage of the formulas that directly address the ranges of interest is that it does not matter if those ranges are moved out of physical alignment.

24 12 15.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	1	2	3	4	5	6	7
3	8	9	10	11	12	13	14
4
5									15	16	17	18	19	20	21
6
7	22	23	24	25	26	27	28
8
9						29	30	31	32	33	34	35
10
11	Row	Col	Return	Post 3	Post 4
12	1	1	1	1	1
13	1	2	2	2	2
14	1	3	3	3	3
15	1	4	4	4	4
16	1	5	5	5	5
17	1	6	6	6	6
18	1	7	7	7	7
19	2	1	22	15	15
20	2	2	23	16	16
21	2	3	24	17	17
22	2	4	25	18	18
23	2	5	26	19	19
24	2	6	27	20	20
25	2	7	28	21	21
26	3	1	0	22	22
27	3	2	0	23	23
28	3	3	0	24	24
29	3	4	0	25	25
30	3	5	0	26	26
31	3	6	29	27	27
32	3	7	30	28	28
33	4	1	#REF!	29	29
34	4	2	#REF!	30	30
35	4	3	#REF!	31	31
36	4	4	#REF!	32	32
37	4	5	#REF!	33	33
38	4	6	#REF!	34	34
39	4	7	#REF!	35	35
Disjoint ranges (4)

Cell Formulas
Range	Formula
D12:D39	D12	=INDEX(FILTER($B$2:$H$9,BYROW($B$2:$H$9<>"",OR)*(SEQUENCE(ROWS($B$2:$H$9))<>2)),B12,C12)
E12:E39	E12	=INDEX(VSTACK(B$2:H$2,J$5:P$5,B$7:H$7,G$9:M$9),B12,C12)
F12:F39	F12	=INDEX((B$2:H$2,J$5:P$5,B$7:H$7,G$9:M$9),1,C12,B12)

 

[Cubist]

...but not only are the earlier ones considerably shorter

Shorter and more flexible sure - assuming the OP has 5 rows of data only. Even though I'm not in the business of speculating, but if I were to speculate out of all the scenarios you demonstrated, having a larger data set is most likely.

 

[Peter_SSs]

but if I were to speculate out of all the scenarios you demonstrated, having a larger data set is most likely.

You could well be right. Hopefully we will get some feedback at some stage.

 

[HighAndWilder]

You could well be right. Hopefully we will get some feedback at some stage.

Apologies for the delay in responding. Many responses to look at for which I am thankful.

Maybe I could have been clearer in my post :

1. The entire data range could be huge and will vary.

2. The rows of interest will change and the number of rows could vary.

3. It is the value at the intersection of a row and column in the given rows that I am after.

The solution from hagia_sofia seems to be the most appropriate.

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

It allows the a range, the entire range in this case, to be referenced only once, this may need to be a dynamic named range, and is flexible with the row numbers which could take the values from a range.

It creates a dynamic array just containing the rows in which I am interested in which is then interrogated.

The way of referencing a range in my post was interesting.

=INDEX($B$2:$H$2:$B$4:$H$4:$B$7:$H$7:$B$9:$H$9,2,3)

It seems that this range is interpreted as $B$2:$H$9 and so

=INDEX($B$2:$H$2:$B$4:$H$4:$B$7:$H$7:$B$9:$H$9,2,3)

will return the value from row 2 of the entire data range ($B$3:$H$3), not the desired result.

 

[djclements]

The way of referencing a range in my post was interesting.

=INDEX($B$2:$H$2:$B$4:$H$4:$B$7:$H$7:$B$9:$H$9,2,3)

It seems that this range is interpreted as $B$2:$H$9

Yes, that's correct. The boundaries of a run-on range reference like this will extend from the minimum column/row to the maximum column/row present in the reference and include everything in between.

For example, =D2:F7:A5:B10 will be interpreted as =A2:F10.

The best use case for this concept is with dynamic spill range references, e.g. =A2#:A1 will extend the boundaries of the spill range A2# to include the row above.

 

[HighAndWilder]

Yes, that's correct. The boundaries of a run-on range reference like this will extend from the minimum column/row to the maximum column/row present in the reference and include everything in between.

For example, =D2:F7:A5:B10 will be interpreted as =A2:F10.

The best use case for this concept is with dynamic spill range references, e.g. =A2#:A1 will extend the boundaries of the spill range A2# to include the row above.

That makes sense now, thanks.