Selecting MAX values of 3D array via INDEX/MATCH

travellerva

New Member
Joined
Mar 31, 2012
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
Book1
BCDE
1AAPLAGMAMDASML
2CloseCloseCloseClose
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
HIJK
3Wk1 HiWk2 Hi
4AAPL=MAX(INDEX(B:B,5):INDEX(B:B,9))=MAX(INDEX(B:B,10):INDEX(B:B,14))
5AGM=MAX(INDEX(C:C,5):INDEX(C:C,9))=MAX(INDEX(C:C,10):INDEX(C:C,14))
6AMD=MAX(INDEX(D:D,5):INDEX(D:D,9))=MAX(INDEX(D:D,10):INDEX(D:D,14))
7ASML=MAX(INDEX(E:E,5):INDEX(E:E,9))=MAX(INDEX(E:E,10):INDEX(E:E,14))
Sheet1


Any ideas? Many thanks.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Here's one way you could do it:

ABCDE
1AAPLAGMAMDASML
2CloseCloseCloseClose
3226.51141.60156.40926.18
4225.89144.85155.28933.15
5226.05143.02148.56918.66
6224.72144.77147.36928.25
7221.72141.75140.75879.58
8221.27140.68141.13869.49
9217.53142.43136.77852.86
10216.24138.14134.27860.55
11213.31137.29136.32876.65
12209.82133.80128.67836.35
13207.23134.92130.18843.01
14209.27134.24134.82820.35
15
1612
17AAPL226.51221.27
18AGM144.85142.43
19AMD156.40141.13
20ASML933.15876.65
21
Sheet1
Cell Formulas
RangeFormula
B17:B20B17=TRANSPOSE(B1:E1)
C17:D20C17=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?
 
Upvote 0
Try:

Book1
ABCDEFGHIJ
1AAPLAGMAMDASML
2CloseCloseCloseClose
3226.51141.6156.4926.18Wk1 HighWk2 High
4225.89144.85155.28933.15AAPL226.05216.24
5226.05143.02148.56918.66AGM144.77138.14
6224.72144.77147.36928.25AMD148.56136.32
7221.72141.75140.75879.58ASML928.25876.65
8221.27140.68141.13869.49
9217.53142.43136.77852.86
10216.24138.14134.27860.55
11213.31137.29136.32876.65
12209.82133.8128.67836.35
13207.23134.92130.18843.01
14209.27134.24134.82820.35
15
Sheet2
Cell Formulas
RangeFormula
I4:J7I4=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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top