I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It returns
Alberts, then Johnson, then Johnson again... So, it's only finding the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5+(ROW(B$1:B$5)/10^10),ROW(A1)),B$1:B$5+(ROW(B$1:B$5)/10^10),0))

Copy down as needed.

Biff

"quailhunter"
wrote in message
...

I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It returns
Alberts, then Johnson, then Johnson again... So, it's only finding the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479

Take a look at Data Filter Autofilter..........it will return the entire
row of the Top5 scores, including duplicates.......

Vaya con Dios,
Chuck, CABGx3



"quailhunter"
wrote in message
...

I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It returns
Alberts, then Johnson, then Johnson again... So, it's only finding the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479

P.S. -

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

My formula will return:

Alberts
Smith
Johnson
Able
Reese

If you want to "flip" Smith and Johnson just change this portion of the
formula in both places:

+(ROW(B$1:B$5)/10^10

Change to:

-(ROW(B$1:B$5)/10^10

Biff

"Biff" wrote in message
...
Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5+(ROW(B$1:B$5)/10^10),ROW(A1)),B$1:B$5+(ROW(B$1:B$5)/10^10),0))

Copy down as needed.

Biff

"quailhunter"
wrote in message
...

I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It returns
Alberts, then Johnson, then Johnson again... So, it's only finding the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread:
http://www.excelforum.com/showthread...hreadid=472479

See also my post in:

http://tinyurl.com/44ywo

quailhunter wrote:
I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It returns
Alberts, then Johnson, then Johnson again... So, it's only finding the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

"quailhunter" wrote...
....
I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It
returns Alberts, then Johnson, then Johnson again... So, it's only
finding the first occurrence of Johnson and not returning Smith for
'3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

Another alternative involving only one cell per result but assuming later
results can rely on earlier results. If your sample data above were in
A1:B5, then try these array formulas.

E1:F1:
=INDEX(A1:B5,MATCH(MAX(B1:B5),B1:B5,0),0)

E2:F2:
=INDEX($A$1:$B$5,MATCH(MAX(IF(COUNTIF(E$1:E1,$A$1: $A$5)
*COUNTIF(F$1:F1,$B$1:$B$5)=0,$B$1:$B$5)),
IF(COUNTIF(E$1:E1,$A$1:$A$5)*COUNTIF(F$1:F1,$B$1:$ B$5)=0,
$B$1:$B$5),0),0)

Select E2:F2 and fill down as needed.

Biff,
Your code worked like a charm. I used the -(ROW(B$1:B$5)/10^10, and got
exactly what I was looking for... Q. How do I modify so the next column
over in my report will display the score for each individual?

Biff Wrote:
P.S. -

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

My formula will return:

Alberts
Smith
Johnson
Able
Reese

If you want to "flip" Smith and Johnson just change this portion of
the
formula in both places:

+(ROW(B$1:B$5)/10^10

Change to:

-(ROW(B$1:B$5)/10^10

Biff

"Biff" wrote in message
...
Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:


=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5+(ROW(B$1:B$5)/10^10),ROW(A1)),B$1:B$5+(ROW(B$1:B$5)/10^10),0))

Copy down as needed.

Biff

"quailhunter"

wrote in message
...

I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX
MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It
returns
Alberts, then Johnson, then Johnson again... So, it's only finding
the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts,
Johnson,
Smith???


--
quailhunter

------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread:
http://www.excelforum.com/showthread...hreadid=472479





--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479

Hi!

How do I modify so the next column
over in my report will display the score for each individual?

Use a different, less complicated lookup formula:

Assume the original table is in A1:B5

The extracted top 5 names are in the range D1:D5

In E1 enter this formula and copy down:

=VLOOKUP(D1,A$1:B$5,2,0)

Biff

"quailhunter"
wrote in message
...

Biff,
Your code worked like a charm. I used the -(ROW(B$1:B$5)/10^10, and got
exactly what I was looking for... Q. How do I modify so the next column
over in my report will display the score for each individual?

Biff Wrote:
P.S. -

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

My formula will return:

Alberts
Smith
Johnson
Able
Reese

If you want to "flip" Smith and Johnson just change this portion of
the
formula in both places:

+(ROW(B$1:B$5)/10^10

Change to:

-(ROW(B$1:B$5)/10^10

Biff

"Biff" wrote in message
...
Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:


=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5+(ROW(B$1:B$5)/10^10),ROW(A1)),B$1:B$5+(ROW(B$1:B$5)/10^10),0))

Copy down as needed.

Biff

"quailhunter"

wrote in message
...

I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX
MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It
returns
Alberts, then Johnson, then Johnson again... So, it's only finding
the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts,
Johnson,
Smith???


--
quailhunter

------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread:
http://www.excelforum.com/showthread...hreadid=472479





--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479

Biff,
Thanks for your excellent feedback. You're a genius...

Thanks again...


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479

You're welcome. Thanks for the feedback!

Biff

"quailhunter"
wrote in message
...

Biff,
Thanks for your excellent feedback. You're a genius...

Thanks again...


--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile:
http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=472479