0

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.

philus
  • 51
  • 1
  • 3
  • 1
    here are examples on how to get the result of two item lookups: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another/42493697#42493697 – Scott Craner Aug 25 '22 at 14:03
  • ooo yeah, thanks! used sumif(ID,CAT=b,VAL=94) – philus Aug 25 '22 at 14:50

1 Answers1

0

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.
Ike
  • 9,580
  • 4
  • 13
  • 29