1

I have Excel data like the below table. My table has duplicate values in COL B but they are attached to unique values in COL A. I want to find the top 5 values and cut-off after 5 rows.Can this be done?

My existing formula is:

=SORT(FILTER(A1:B7,B1:B7>=LARGE(A1:B7,5),2,-1))

Column A Column B
Name A 56
Name B 20
Name C 30
Name D 20
Name E 45
Name F 38
Name G 20

1 Answer 1

0

Wrap it in INDEX and use two SEQUENCE to only return the top 5 and the 2 columns:

=INDEX(SORT(FILTER(A1:B7,B1:B7>=LARGE(A1:B7,5)),2,-1),SEQUENCE(5),SEQUENCE(,2))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.