Forum Discussion

Harun24HR's avatar
Harun24HR
Bronze Contributor
Nov 09, 2025
Solved

How to find top 3 from each category using Excel dynamic formula?

I have many data with ranking percentage. Here is few of a sample. I want to find top3 of ranking from each category by a single cell dynamic spill formula.   
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Nov 09, 2025

    One possibility, using the same basic concept of my INSTANCENUM function:

    =LET(
   arr, SORT(A2:B20, {1,2}, {1,-1}),
   key, TAKE(arr,, 1),
   FILTER(arr, 4 > SCAN(0, key = DROP(VSTACK("", key), -1), LAMBDA(a,v, 1 + a * v)))
)

    Or, as a curried LAMBDA function:

    =LAMBDA(arr,FILTER(arr,4>LAMBDA(key,SCAN(0,key=DROP(VSTACK("",key),-1),LAMBDA(a,v,1+a*v)))(TAKE(arr,,1))))(SORT(A2:B20,{1,2},{1,-1}))

    p.s. I think everyone is currently having issues with file attachments being removed from their posts.

Resources