Home > OS >  Building Sorting/Ranking into an Index Match Array formula?
Building Sorting/Ranking into an Index Match Array formula?

Time:03-19

I'm trying to sort the results of an array Index Match function and haven't had much luck.

My data set is:

Salary (000s) | Team  | Player
500           | Bears | Frank
900           | Bears | Paul
1200          | Bears | Andy
100           | Bears | Greg
600           | Bears | Larry
900           | Tigers| Moe
1800          | Tigers| Harry
125           | Tigers| Vince

I want to extract an array of all Bears players in descending order of their salary using a formula. I CANNOT sort the table itself.

The desired outcome is the following list if using Bears as the target team:

Andy
Paul
Larry
Frank
Greg

Is this possible? The closest I have come is the below:

=IFERROR(INDEX($D$3:$D$10,LARGE(IF($C$3:$C$10=$F$3,ROW($D$3:$D$10)-MIN(ROW($D$3:$D$10)) 1),ROWS($J$3:J3))),"")

The problem is, this formula just pulls in names in order of occurrence in the column. (Which again will not work for my particular scenario.) Ideally, I'd like to solve this with only the formula to avoid having to build a helper column into the datasheet.

I'd really appreciate any guidance anyone could offer on how to improve this equation to pull names in descending order of salary. Thank you very much for your time and your help.

enter image description here

Formula in E2:

=INDEX(SORT(FILTER(A2:C9,B2:B9="Bears"),,-1),0,3)

Or, for those who allready have TAKE() and DROP() may try:

=TAKE(SORT(FILTER(A2:C9,B2:B9="Bears"),,-1),,-1)

Or:

=DROP(SORT(FILTER(A2:C9,B2:B9="Bears"),,-1),,2)
  • Related