top values and corresponding name

[gdecat]

I am trying to pull the top 10 values from row B and the name
associtated with that value in row A. I will be doing the same things
with Rows C and A, and the again with Rows D and A, so I can't simply
sort.

Example:
Name Points 3's FT's
Greg 24.5 23 2
Matt 25 11 3
Tom 22.3 22 4
Dave 15.5 15 5

I need to return for points:
Top Scorer's
Matt 25
Greg 24.5
Tom 22.3
Dave 15.5

I would then be returning the same situation for top 5 in 3's and the
top 5 in FT's.

I tried using the large() function to pull out the top 10 in Points,
but did not no how to pull the names with them.

 

[Max]

Here's one non array-way which also caters
for the event of ties/multiple ties in the values

Sample implementation at:
http://cjoint.com/?mdx41dXuzj
Extracting Top Values and Corresponding Names.xls

Assuming the source table is in Sheet1,
cols A to D, data from row2 down

Use 3 empty cols to the right, say cols F to H
Put in F2: =IF(B2="","",B2-ROW()/10^10)
Copy F2 to H2, fill down to say, H20
to cover the max expected extent of data
(Leave F1:H1 empty)

In a new Sheet2,

Put in A2:
=IF(ISERROR(LARGE(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
Copy A2 to B2, fill down to B20
(cover the same extent as the range filled in Sheet1)

The above returns a full descending sort of the Names and the Points

Put in D2:
=IF(ISERROR(LARGE(Sheet1!$G:$G,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0)))

Put in E2:
=IF(ISERROR(LARGE(Sheet1!$G:$G,ROWS($A$1:A1))),"",INDEX(Sheet1!C:C,MATCH(LAR
GE(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0)))

Select D2:E2, fill down to E20

The above returns a full descending sort of the Names and the 3's

Put in G2:
=IF(ISERROR(LARGE(Sheet1!$H:$H,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$H:$H,ROWS($A$1:A1)),Sheet1!$H:$H,0)))

Put in H2:
=IF(ISERROR(LARGE(Sheet1!$H:$H,ROWS($A$1:A1))),"",INDEX(Sheet1!D,MATCH(LAR
GE(Sheet1!$H:$H,ROWS($A$1:A1)),Sheet1!$H:$H,0)))

Select G2:H2, fill down to H20

The above returns a full descending sort of the Names and the FT's

Just pick the top 5 as desired from each group. In the event of
ties/multiple ties occuring you may need to pick more than just the top 5
lines. Tied lines, if any, will appear in the same relative order that they
are within the source table.

 

[pinmaster]

Hi, how about:

=INDEX(A1:A10,MATCH(LARGE(B1:B10,1),B1:B10,0))

HTH
JG

 

[gdecat]

Max your suggestion s worked great.

Thanks

 

[Max]

Glad to hear that !
Thanks for the feedback ..