Top N values basing on various criteria

user_15622958 0
Viewed 15

I have a simple table of characters, their gender and stats.

Character Gender Strength Constitution Agility
Alice F 15 10 7
Bob M 10 15 8
Charlie F 8 14 5
Dylan M 9 9 17

I wanted to make a Best 3 table basing on some criteria, eg.

Best 3, Strength:

No. All Female Male
1 Alice Alice Bob
2 Bob Charlie Dylan
3 ... ... ...

To get the strongest among all people I simply use:

=LARGE(C2:C5;1)

To get the strongest among females I know I need something like:

=IF(COUNTIFS({B2:B5};"F");LARGE(C2:C5;1))

Where {B2:B5} is a range of the "Gender" column, C2:C5 is a range for "Strength" column. But this gives me the value from the "Strength" column (15), and I need a corresponding value from the "Character" column (Alice). I don't know how to nest another function that will give me the corresponding name.

I would also be grateful if the function would give all the names that match the criterion, eg. if there was Elliot with strength 15, his name should be displayed along with Alice. But I have no clue how to ever approach this problem.

google-sheets-formula google-sheets
1 Answers

For the top 3 'All' try

=query(A2:E5, "Select A where B <>'' order by C desc limit 3", 0)

and you can use the same logic for the top 3 'Male'

=query(A2:E5, "Select A where B = 'M' order by C desc limit 3", 0)

and 'Female'

=query(A2:E5, "Select A where B = 'F' order by C desc limit 3", 0)

See if that works for you?

Reference

user_38894230
Related