[SOLVED] Google Sheets function to return top column value based on highest average in another value, per unique ID

Google Sheets function to return top column value based on highest average in another value, per unique ID

I am tracking values for unique IDs across countries. A unique ID could have multiple data rows in the same country, but with different amounts. I want to have a dropdown where upon selecting the unique ID, you are given the top and second performing country based on the average of that countries amounts per that unique ID. Thank you! Link for example sheet here:

https://docs.google.com/spreadsheets/d/1cx1_V3TVsGinKrFjHYeZYFWptn5KjemOxMMQMFP5v7w/edit?gid=0#gid=0

Trying to do stuff with index/filter/sort but having trouble with the criteria/matrix

Solution

You can add this formula to F2 and it will populate for both F2 and G2.

=LET(data,FILTER(A:C,B:B=E2),iferror(torow(CHOOSECOLS(QUERY(data,"select Col1,avg(Col3) group by Col1 order by avg(Col3) desc limit 2 label Col1 '', avg(Col3) ''"),1),1)))