Search code examples
sortinggoogle-sheetsfiltermax

Return Top value plus ties for each change in another Column value

Using Columns A to J, I am looking to return the answer in Columns L to N.

I am trying to get the maximum value (plus ties) of Column B for each Tier (Column A) and show the corresponding fixture (Column J).

Example

I have managed to get close with various attempts but nothing that ticks all the boxes but missing something obvious I think.

Can anyone help please?

Various SortN/Filter/Queries have been tried but this is just beyond my level of expertise.

Solution

  • You can try using this formula

    =LET(z,tocol(,1),tiers,UNIQUE(A2:A),choosecols(reduce(z,tiers,lambda(a,c,let(individual,FILTER(A:J,A:A=c),VSTACK(a,filter(individual,index(individual,,2)=max(index(individual,,2))))))),1,2,10))