excelsortingexcel-formulahstack

For each unique value in a column1 find the maximum value in Column3 and the corresponding value in Column3 using excel

Let's say we have a dataframe in Sheet 1 of the workbook and the desired output in sheet 2 is to return the maximum value in Var3 for each unique value in Var1 and the corresponding value in Var2.

Dataframe:

enter image description here

Desired output:

enter image description here

The code below only gives the maximum value of all values as shown below:

=TAKE(SORT(HSTACK(INDEX(Sheet1!B2:C19,,MATCH(E1,Sheet1!C1,0)),CHOOSECOLS(Sheet1!A2:C19,1),CHOOSECOLS(Sheet1!A2:C19,2)),1,-1),5)

enter image description here

Solution

  • Try the following formula:

    enter image description here

    • Formula used in cell E3

    =LET(
     a, INDEX(A2:C19,,XMATCH(J1,A1:C1)),
     CHOOSECOLS(FILTER(A2:C19,ISNUMBER(XMATCH(a,MAXIFS(a,A2:A19,UNIQUE(A2:A19))))),{1,3,2}))