Top 5 values using LARGE and INDEX avoiding duplicates

[carl916]

Hi

I am trying to get the top 5 values in a list and return some text from an adjacent column. I have names in cells A1:A20 and some numeric value in cells B1:B20. I am looking to get the top 5 names from col A based the values in on col B.

I have tried using LARGE, INDEX and LOOKUPs which almost work but I can not account for two cells having the same value; the end result is that one of the names gets duplicated. I found a post which comes close but I can't get it working; it uses an IF and an OFFSET to check if two values match.
http://www.pcreview.co.uk/forums/thread-3154173.php

I could only get the equation in the above function to return the top 5 values plus 1 (i.e. items 2,3,4,5 & 6).
Could someone please explain or simply the above post, or provide an alternative solution?

Regards, Carl Gilbert

[VBA Noob]

Does this example do what your after. Needs to be entered with Ctrl + Shift + enter

VBA Noob

[daddylonglegs]

Here's an alternative using a "helper" column. Using VBA Noob's setup, insert a new column A to the left of your names and use this formula in A1 copied down to give a unique rank

=RANK(C1,C$1:C$16)+COUNTIF(C$1:C1,C1)-1

then in G1 copied down to list the names in order

=VLOOKUP(ROWS(G$1:G1),A$1:B$16,2,0)

[TheSageBR]

Thanks!

[Ron Coderre]

Perhaps something like this.....

With A1:B20 containing this list:
Alpha	100
Bravo	90
Charlie	20
Delta	80
Echo	70
Foxtrot	120
Golf	(blank)
Charlie	80
India	50
Juliet	40
Kilo	30
Lima	20
Mike	10
November(blank)	
Oscar	(blank)
Papa	(blank)
Quebec	(blank)
Romeo	47
Sierra	61
Tango	45

Notice:
80 is the 4th and 5th largest value
Charlie appears twice (at 2 and 80)

These regular formulas returns the top_5 values in the order in which they appear in the list.

This one returns the name
C1: =INDEX($A$1:$A$20,MATCH(LARGE(INDEX($B$1:$B$20+(ROWS($B$1:$B$20)-ROW($B$1:$B$20))/10^5,0),ROWS($1:1)),INDEX($B$1:$B$20+(ROWS($B$1:$B$20)-ROW($B$1:$B$20))/10^5,0),0))

This one returns the associated value
=LARGE($B$1:$B$20,ROWS($1:1))

Copy both formulas down through Row_5

Using my posted example...
These values are returned:
Foxtrot	120
Alpha	100
Bravo	90
Delta	80
Charlie	80

Is that something you can work with?