Excel get info about maximum value in a variable defined range

Question

assume I have the following set of data :

ID CAT VAL

a   a   4

b   a   94

c   b   5

d   b   94

e   c   2

f   c   3

In Excel 2019 Pro get the maximum VAL of CAT=b using MAXIF(VAL,CAT=b) and I get 94. Now I want to get the ID of the corresponding value (i.e. ID=d), but I cannot use INDEX+MATCH since the maximum of CAT=a is also 94 and then I get ID=b which is not what I want.

How to get around that??

Thanks, many of them.

Answer 1

if you have Excel 365 you can use this formula:

=INDEX(SORT(FILTER(A2:C7,B2:B7="b"),3,-1),1,1)

• It first filters all CAT = B rows.
• Sorts them by column VAL Descending --> the highest VAL-row is at the top
• Then returns via INDEX the first column of the first row.