How to find the highest N values in each group in Google Sheets-CodePudding

I want to use a formula to find the highest N values in each group in a Google Spread Sheets.

I tried this formula from

This is what I expect:

What is wrong with it?

CodePudding user response：

You have to put a comma, not a semi colon before IFERROR. It's creating two columns, one twice larger than the other instead of three columns ;)

=ArrayFormula(QUERY({SORT(A2:B,1,true,2,false),IFERROR(row(A2:A)-match(query(SORT(A2:B,1,true,2,false),"Select Col1"),query(SORT(A2:B,1,true,2,false),"Select Col1"),0))},"Select Col1,Col2 where Col3<3"))

CodePudding user response：

alternative formula:

=QUERY(SORT({{A2:B}\MAP(A2:A;B2:B;lambda(ax;bx;IFERROR(Rank(bx;Filter(B$2:$B;A$2:$A=ax);0);IFERROR(1/0))))};1;0;3;1);"Select Col1, Col2 Where Col3<3")