Finding Max Value and Returning Horizontal Array belonging to the Max Value

Question

I attempted to use a mixture of filter/index/xlookup with multiple criteria.

I am given 4 rows of data (Option 1-4) for 3 different groups (A-C). I need to compare the max V1 value for each group for each option, and will need to export the corresponding data row corresponding to the max v1 value.

	V1	V2	V3	V4	V5
Option 1	1	1	1	1	1
Option 2	50	49	48	47	46
Option 3	3	3	3	3	3
Option 4	4	4	4	4	4
Option 1	8	8	8	8	8
Option 2	9	9	9	9	9
Option 3	100	99	98	10	10
Option 4	11	11	11	11	11
Option 1	20	20	20	20	20
Option 2	21	21	21	21	21
Option 3	22	22	22	22	22
Option 4	2000	23	23	23	23

For example: Comparing Option 4 across group A-C The max value of V1 is in option 4 --> 2000 Then returning the row the value belongs to --> 2000 23 23 23 23 23

	V1	V2	V3	V4	V5
Option 1	20	20	20	20	20
Option 2	50	49	48	47	46
Option 3	100	99	98	10	10
Option 4	2000	23	23	23	23

How do i achieve this?

Harun24hr · Accepted Answer · 2025-10-30 03:00:29Z

2

Try REDUCE() with combinations few of a other functions.

=REDUCE(HSTACK("",C3:G3),UNIQUE(B4:B15),LAMBDA(a,x,
VSTACK(a,TAKE(SORT(FILTER(HSTACK(B4:G15,BYROW(C4:G15,MAX)),B4:B15=x),7,-1),1,COLUMNS(B4:G4)))))

answered Oct 30 at 3:00

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Foxfire And Burns And Burns · Accepted Answer · 2025-10-30 11:10:19Z

0

For older versions of Excel you may use SUMPRODUCT combined with INDEX and MATCH for this.

Formula in cell J2:

=SUMPRODUCT(MAX(--($A$2:$A$13=I2)*$B$2:$B$13))

Then drag down the formula for whole column.

Formula in cell K2:

=INDEX($C$1:$F$13;SUMPRODUCT(--($A$2:$A$13=$I2)*--($B$2:$B$13=$J2)*ROW($A$2:$A$13));MATCH(K$1;$C$1:$F$1;0))

Then drag down and to the right until V5.

answered Oct 30 at 11:10

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges42 bronze badges

Comments

Ron Rosenfeld · Accepted Answer · 2025-10-30 12:37:20Z

0

You can use GROUPBY to find the maximum V1 for each option. Then use that result to find the matching row value and return that:

=LET(
    _data, A:.F,
    _maxV1, GROUPBY(CHOOSECOLS(_data, 1), CHOOSECOLS(_data, 2), MAX, 1, 0),
    _rows, BYROW(
        _maxV1,
        LAMBDA(arr,
            XMATCH(
                1,
                (INDEX(arr, 1) = CHOOSECOLS(_data, 1)) *
                    (INDEX(arr, 2) = CHOOSECOLS(_data, 2))
            )
        )
    ),
    VSTACK(CHOOSEROWS(_data, 1), CHOOSEROWS(_data, _rows))
)

answered Oct 30 at 12:37

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Comments

user22566114 · Accepted Answer · 2025-10-30 18:38:05Z

0

I'd use Power Query for this. With Office 365 this formula could be an alternative. It doesn't require LAMBDA.

=LET(_data,A1:F13,
_header,DROP(TAKE(_data,1),,1),
_body,DROP(_data,1),
VSTACK(HSTACK("",_header),
CHOOSEROWS(_body,
XMATCH(
MAXIFS(
INDEX(_body,,2),INDEX(_body,,1),UNIQUE(INDEX(_body,,1)))&UNIQUE(INDEX(_body,,1)),
INDEX(_body,,2)&INDEX(_body,,1)))))

answered Oct 30 at 18:38

user22566114

5464 silver badges6 bronze badges

Comments

P.b · Accepted Answer · 2025-10-30 19:37:16Z

0

Another possibility using M365:

=LET(a,A2:F13,
     s,SORT(HSTACK(a,ROW(a)-1,-INDEX(a,,2)),8),
CHOOSEROWS(a,VLOOKUP(UNIQUE(TAKE(a,,1)),s,7,)))

answered Oct 30 at 19:37

P.b

12k2 gold badges15 silver badges34 bronze badges

Collectives™ on Stack Overflow

Finding Max Value and Returning Horizontal Array belonging to the Max Value

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related