I'm sorry to be critical, but this approach drifts way off of accepted best practices, and then requires extensive manual intervention. This can be accomplished in 2 formulas and be fully dynamic, requiring no manual editing of the selected list or having to pull down formulas manually. In cell E4 enter: =SORT(FILTER(A2:B102,B2:B102>=LARGE(B2,B102,10)),2,-1) This dynamically creates the top 10 table with no manual intervention of any kind. Then in G4 place: =BYROW( CHOOSECOLS( E4#, 2 ), LAMBDA(r, RANK.EQ( r, INDEX( E4#,, 2 ), 0 ) ) ) The built-in RANK.EQ() function already handles ties correctly. Actually, RANK.EQ is smart enough to detect if a column in an array contains numeric values, so you could get away with: =BYROW( CHOOSECOLS( E4#, 2 ), LAMBDA(r, RANK.EQ( r, E4# ) ) ) But I would avoid that to prevent ambiguity.
Thank you so much for sharing this! Your approach is incredibly efficient and dynamic, and it offers a great solution for advanced users who want minimal manual intervention. I appreciate you breaking it down step-by-step for clarity-this is a valuable addition to the tutorial! The method I demonstrated was designed to introduce foundational Excel functions step-by-step, catering to users who are still building their confidence with these tools. Thanks again for sharing your expertise-it's always great to see different approaches to solving Excel challenges!
I'm sorry to be critical, but this approach drifts way off of accepted best practices, and then requires extensive manual intervention. This can be accomplished in 2 formulas and be fully dynamic, requiring no manual editing of the selected list or having to pull down formulas manually.
In cell E4 enter:
=SORT(FILTER(A2:B102,B2:B102>=LARGE(B2,B102,10)),2,-1)
This dynamically creates the top 10 table with no manual intervention of any kind. Then in G4 place:
=BYROW( CHOOSECOLS( E4#, 2 ), LAMBDA(r, RANK.EQ( r, INDEX( E4#,, 2 ), 0 ) ) )
The built-in RANK.EQ() function already handles ties correctly. Actually, RANK.EQ is smart enough to detect if a column in an array contains numeric values, so you could get away with:
=BYROW( CHOOSECOLS( E4#, 2 ), LAMBDA(r, RANK.EQ( r, E4# ) ) )
But I would avoid that to prevent ambiguity.
Thank you so much for sharing this! Your approach is incredibly efficient and dynamic, and it offers a great solution for advanced users who want minimal manual intervention. I appreciate you breaking it down step-by-step for clarity-this is a valuable addition to the tutorial!
The method I demonstrated was designed to introduce foundational Excel functions step-by-step, catering to users who are still building their confidence with these tools.
Thanks again for sharing your expertise-it's always great to see different approaches to solving Excel challenges!