Finding the Max of Excel Matrix Data based on Criteria from Maxtrix-CodePudding

I have a data on Matrix and I also have the criteria data in Matrix as well See below

Data from the Matrix

Period	0.0	30	45	60	75	90	105	120	135	150	180
6.0	0.356	0.443	0.469	0.505	0.579	0.525	0.516	0.475	0.342	0.271	0.171
7.0	0.439	0.541	0.558	0.678	0.802	0.642	0.747	0.499	0.436	0.336	0.232
8.0	0.505	0.544	0.591	0.694	0.759	0.747	0.736	0.584	0.560	0.467	0.269
9.0	0.489	0.614	0.618	0.630	0.791	0.687	0.631	0.577	0.507	0.562	0.340
10.0	0.538	0.603	0.572	0.580	0.703	0.643	0.619	0.556	0.489	0.459	0.399
11.0	0.503	0.491	0.513	0.578	0.585	0.630	0.587	0.542	0.439	0.459	0.345
12.0	0.517	0.446	0.539	0.588	0.546	0.564	0.552	0.497	0.411	0.412	0.355
13.0	0.470	0.439	0.545	0.534	0.530	0.482	0.510	0.470	0.422	0.404	0.329
14.0	0.399	0.427	0.469	0.442	0.462	0.434	0.409	0.425	0.382	0.395	0.340
15.0	0.370	0.390	0.388	0.397	0.421	0.393	0.355	0.387	0.355	0.341	0.331

Criteria for the matrix

Period	0.0	30	45	60	75	90	105	120	135	150	180
6.0	3	5	5	6	7	6	6	5	3	2	0
7.0	5	6	7	9	10	8	10	6	5	3	1
8.0	6	6	7	9	10	10	9	7	7	5	2
9.0	6	8	8	8	10	9	8	7	6	7	3
10.0	6	7	7	7	9	8	8	7	6	5	4
11.0	6	6	6	7	7	8	7	6	5	5	3
12.0	6	5	6	7	6	7	7	6	4	4	3
13.0	5	5	6	6	6	5	6	5	4	4	3
14.0	4	5	5	5	5	5	4	5	4	4	3
15.0	4	4	4	4	4	4	3	4	3	3	3

Is there any way to find the maximum of no 3 or 10 from the criteria data on the criteria Matrix, and the max values should be taken the matrix data based on the location from the matrix criteria ?

So from the above No 10 should be the maximum from Matrix ( [7,75] or [7,105] or [8,75] or [8,90] or [9,75] )?

I am expecting Excel function or VBA to find the max data of those numbers? Thanks alot for your help and taught about it

Excel Function or Excel VBA

CodePudding user response：

Assume tables start (with header row and column) in cell A1 of two sheets named Criteria and Data:

=MAX(SUMPRODUCT( (Criteria!B2:L11=10) * (Data!B2:L11) ) )

CodePudding user response：

Max in Matrix Using Criteria Matrix

If you have Microsoft 365 and if the criteria are in the range N2:N12, in cell O2 of sheet Criteria you could use:

=MAX(TOCOL(($B$2:$L$11=N2)*Data!$B$2:$L$11))

or (more of the same i.e. getting familiar with the LET function)

=LET(tCriteria,$B$2:$L$11,tData,Data!$B$2:$L$11,Criteria,N2,
    MAX(TOCOL((tCriteria=Criteria)*tData)))

used in cell P2 of the screenshot, and copy down.