Site icon ExcelNotes

How to Get the Top 3 Items in a List by Formula

When you work with data directly, you can sort based on a column and then easily get the top values. For example, sort value from largest to smallest to decide the top 3 values, then get the items based on the values.

However, when you work with Pivot Table which contains a lot of communities, it is getting tricky to pick up the top 3 values because the value changes in different communities.

To have the maximum value:

=INDEX(A2:A6, MATCH(MAX(B2:B6),B2:B6,0))

To have the second largest value:

=INDEX(A2:A6, MATCH(LARGE(B2:B6, 2),B2:B6,0))

To have the third largest value:

=INDEX(A2:A6, MATCH(LARGE(B2:B6, 3),B2:B6,0))

Exit mobile version