Returning top 5 values for two columns.. no lists

[paris3]

Spent a few hours on this one and just cant figure it out.

Assume I have the following data:


Apples 5
Oranges 3
Grapes 0
Kiwis 7
Pears 1
Plums 2
Figs 0
Bananas 2

I need a formula that returns the top five values in COL B, and then returns the corresponding text from COL A in another column. So the answer should show as:

Kiwis 7
Apples 5
Oranges 3
Plums 2
Bananas 2


Here's a wrinkle. I can't use AutoList because the values in COL B are returned from an existing array formula which is snipping and transposing data from another sheet. Autolists can't include array formulas.

I dont want to use a pivot table on this either, as the end user of this sheet is "pivot table challenged." It has to report the data dynamically as its entered.

I know how to return the top 5 VALUES (column B) using the LARGE function, but how do I then get it to report the TEXT (from column A) in another column?

[Domenic]

Try the following...

D1, copied down to D5 and over to Column E:

=INDEX(A$1:A$8,MATCH(LARGE($B$1:$B$8-ROW($B$1:$B$8)/10^10,ROWS(D$1:D1)),$B$1:$B$8-ROW($B$1:$B$8)/10^10,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

[paris3]

That worked, thanks! I have to study that solution... very interesting!

As I get better at Excel I hope to post solutions here, and not just ask questions. Thanks again !