Return top 5 results w/corresponding text from adjacent cells

[jsmifc]

Greetings All,

Brand new to these forums, so please bear with me, as I'm sure this has probably been asked plenty of times in the past (unfortunately, I haven't been able to get any of the formulas I've seen to work). Here it goes...

Assume I have the following data:

Column J (J6:J14)
5
3
0
7
1
2
0
2

Column M (M6:M14)
Apples
Oranges
Grapes
Kiwis
Pears
Plums
Figs
Bananas

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

7 Kiwis
5 Apples
3 Oranges
2 Plums
2 Bananas

I suspect the answer utilizes the "Index", "Match" & "Large" functions in some form, but I haven't been able to make it work (particularly when there are several answers with the same numerical value in column J). I've literally spent hours on this, to no avail, so I've kind of given up at this point. If anyone out there can post an actual formula, I would greatly appreciate it! Thanks for any help!

[oeldere]

dutch d1

Please Login or Register  to view this content.

english d1

Please Login or Register  to view this content.

and drag down

[jsmifc]

Hi oeldere... Thanks for the quick reply. Unfortunately, when I enter that formula, I get the following: #NUM!

Is there something I'm doing wrong?

[oeldere]

What the rownumber you put the formula in?

[jsmifc]

The formula is being put in cell J32.

[oeldere]

Please Login or Register  to view this content.

[jsmifc]

Okay, looks like some progress, but here's where I've repeatedly got stuck... It is listing plums twice, rather than plums and bananas (I'm guessing because they both have the same corresponding numerical # (2).

[oeldere]

I noticed that, but just solve 1 problem at the same time.

Add an helpcolumn to make the value unique.

In my case I use column I.

I6 =

Please Login or Register  to view this content.

and drag down.

then the formula in J32 will be:

Please Login or Register  to view this content.

[jsmifc]

Sorry, I'm not familiar with "helpcolumn"... Nor am I sure what you mean by "make the value unique" (perhaps this is why I can't get it to work).
Also, are you saying I need to put the formula J6-Row()/1000000 into cell I6?

[oeldere]

Yep, see the attached file.

[jsmifc]

BAM! Oeldere, you rock! Not sure how or why that worked, but it's working now! I'll need to "reverse" research the reason that worked (because I'm weird like that). Very cool! Thank you so much!

[oeldere]

Using the formula in column I, makes ALL the values unique.

It divide the rownumber:

I 6 = row 6 => 6 / 10000

I 7 = row 7 => 7 / 10000

Thanks for the reply.

Glad I could help.

If your question is solved, will you mark your question as solved.

If you have (other) questions on this item, just ask or comment.

You can add a rep. (point) to the member who helped you.

In that case you have to click on the star (*) on the left side of the topic.