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 infoinspired.com (credit to Prashanth):
=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"))
But all it return is an Array_Literal error:
What is wrong with it?
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"))