Help with displaying values corresponding to top 5 records from a table

[slushman]

Hi there, I am looking to do something whereby if these are my values:

A B
red 0
blue 5
yellow 4
orange 2
green 1
purple 1
black 0
pink 4

I want to have display somewhere else the results like this:

1. blue
2. yellow
3. pink
4. orange
5. green

I'm not sure how to do this though, please help?

[benishiryo]

welcome to the forum, slushman. assuming data in A1:B8, try this array formula in D1:
=IFERROR(INDEX($A$1:$A$8,MATCH(LARGE($B$1:$B$8-ROW($B$1:$B$8)/1000,ROWS(D$1:D1)),$B$1:$B$8-ROW($B$1:$B$8)/1000,0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[slushman]

Hi

Thank You for the reply, I have tried this, though my original data is on a much larger range and I'm trying to generate the results on another Excel tab other than the one the data is on.

The results I am trying to display are on a tab called Feedback and the raw data is on a tab called Scores, the data range is - numerical values AD10:AD57 and the categories I want to display are on B10:B57, so I have written the formula as follows:

=IFERROR(INDEX(Scores!$B$10:$B$57,MATCH(LARGE(Scores!$AD$10:$AD$57-ROW(Scores!$AD$10:$AD$57)/1000,ROWS(B$44:b44)),Scores!$AD$10:$AD$57-ROW(Scores!$AD$10:$AD$57)/1000,0)),"")

This has worked for the top value, how do I amend this to display the second most popular, 3rd, 4th and so on?

[slushman]

Thanks for your help I've figured the rest out