Selecting MAX values of 3D array via INDEX/MATCH

[travellerva]

Book1
	B	C	D	E
1	AAPL	AGM	AMD	ASML
2	Close	Close	Close	Close
3	$ 226.51	$ 141.60	$ 156.40	$ 926.18
4	$ 225.89	$ 144.85	$ 155.28	$ 933.15
5	$ 226.05	$ 143.02	$ 148.56	$ 918.66
6	$ 224.72	$ 144.77	$ 147.36	$ 928.25
7	$ 221.72	$ 141.75	$ 140.75	$ 879.58
8	$ 221.27	$ 140.68	$ 141.13	$ 869.49
9	$ 217.53	$ 142.43	$ 136.77	$ 852.86
10	$ 216.24	$ 138.14	$ 134.27	$ 860.55
11	$ 213.31	$ 137.29	$ 136.32	$ 876.65
12	$ 209.82	$ 133.80	$ 128.67	$ 836.35
13	$ 207.23	$ 134.92	$ 130.18	$ 843.01
14	$ 209.27	$ 134.24	$ 134.82	$ 820.35
Sheet1

I need to finds a generic formula that identifies MAX values from a subset array in the 3D array indexed by a column header value (e.g. the MAX value of AAPL during a week of 5 values.). It can be found by the formula: =MAX(INDEX(B:B,5):INDEX(B:B,9)), but it needs variables for the column selection (B-E) and row range (5-9 and 10-14 in the example shown.

This is the table I'm trying to generate:

Book1
	H	I	J	K
3		Wk1 Hi	Wk2 Hi
4	AAPL	=MAX(INDEX(B:B,5):INDEX(B:B,9))	=MAX(INDEX(B:B,10):INDEX(B:B,14))
5	AGM	=MAX(INDEX(C:C,5):INDEX(C:C,9))	=MAX(INDEX(C:C,10):INDEX(C:C,14))
6	AMD	=MAX(INDEX(D:D,5):INDEX(D:D,9))	=MAX(INDEX(D:D,10):INDEX(D:D,14))
7	ASML	=MAX(INDEX(E:E,5):INDEX(E:E,9))	=MAX(INDEX(E:E,10):INDEX(E:E,14))
Sheet1

Any ideas? Many thanks.

 

[StephenCrump]

Here's one way you could do it:

	A	B	C	D	E
1		AAPL	AGM	AMD	ASML
2		Close	Close	Close	Close
3		226.51	141.60	156.40	926.18
4		225.89	144.85	155.28	933.15
5		226.05	143.02	148.56	918.66
6		224.72	144.77	147.36	928.25
7		221.72	141.75	140.75	879.58
8		221.27	140.68	141.13	869.49
9		217.53	142.43	136.77	852.86
10		216.24	138.14	134.27	860.55
11		213.31	137.29	136.32	876.65
12		209.82	133.80	128.67	836.35
13		207.23	134.92	130.18	843.01
14		209.27	134.24	134.82	820.35
15
16			1	2
17		AAPL	226.51	221.27
18		AGM	144.85	142.43
19		AMD	156.40	141.13
20		ASML	933.15	876.65
21
Sheet1

Cell Formulas
Range	Formula
B17:B20	B17	=TRANSPOSE(B1:E1)
C17:D20	C17	=LET(d,$B$3:$E$14,MAX(IF(($B$1:$E$1=$B17)*(1+INT(SEQUENCE(ROWS(d),,0)/5)=C$16),d)))
Dynamic array formulas.

But it relies on each week having exactly five rows, which I suspect will not always be the case.

Perhaps better to have an adjoining column of dates, and use the date values rather than relying on weeks always having five values?

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1		AAPL	AGM	AMD	ASML
2		Close	Close	Close	Close
3		226.51	141.6	156.4	926.18				Wk1 High	Wk2 High
4		225.89	144.85	155.28	933.15			AAPL	226.05	216.24
5		226.05	143.02	148.56	918.66			AGM	144.77	138.14
6		224.72	144.77	147.36	928.25			AMD	148.56	136.32
7		221.72	141.75	140.75	879.58			ASML	928.25	876.65
8		221.27	140.68	141.13	869.49
9		217.53	142.43	136.77	852.86
10		216.24	138.14	134.27	860.55
11		213.31	137.29	136.32	876.65
12		209.82	133.8	128.67	836.35
13		207.23	134.92	130.18	843.01
14		209.27	134.24	134.82	820.35
15
Sheet2

Cell Formulas
Range	Formula
I4:J7	I4	=LET(c,INDEX($B:$E,0,MATCH($H4,$B$1:$E$1,0)),r,COLUMNS($I:I)*5,MAX(INDEX(c,r):INDEX(c,r+4)))

Stephen raises a very good point about the weeks potentially not being exactly 5 days. His results and mine are slightly different as well, since he starts at row 3, and I start at row 5 like your example. This could also be resolved with a date column.

 

[travellerva]

Thanks, guys! That's really creative stuff. I was struggling with how to identify the column and row numbers and was not familiar with the function LET. It works fine. I had deliberately simplified the sample spreadsheet for this thread; my actual production version has 65 columns and 1500 rows.
Your suggestions work perfectly. Thanks a lot!