Cliff,

=INDEX(A2:A5,MATCH(LARGE(D2:D5,1),D2:D5,0))

where A2:A5 contains Players, D2:D5 your AVG's.

Aladin

===============

Aladin, this works up to a point, but there is a

problem with duplicate data. For example, if Alpha(AVG)

and Bravo(Avg) both =.300, then

=INDEX(A2:A5,MATCH(LARGE(D2:D5,1),D2:D5,0))

returns Alpha

=INDEX(A2:A5,MATCH(LARGE(D2:D5,2),D2:D5,0))

returns Alpha

=INDEX(A2:A5,MATCH(LARGE(D2:D5,3),D2:D5,0))

returns Charlie.

Thanks,

Cliff

Cliff,

I should have known that there would be duplicate average points. But then it seems you want to rank your players from highest to lowest? Right?

Aladin

==============

,

Cliff,

Lets start over. Here is your sample data, a bit modified,

{"Player","HR","RBI","AVG";"Alpha",23,100,0.3;"Bravo",19,93,0.3;"Charlie",17,85,0.4;"Delta",13,67,0.276}

which occupy A1:D5.

In E2 enter: =RANK(D2,D$2:D$5)+COUNTIF(D$2:D2,D2)-1

Copy down as far as needed.

In G2 enter: =IF(ROW()-1<=COUNT($D$2:$D$5),INDEX(A$2:A$5,MATCH(ROW()-1,$E$2:$E$5,0)),"")

Copy down as far as needed.

The worksheet would now look like this:

{"Player","HR","RBI","AVG",0,0,0;"Alpha",23,100,0.3,2,0,"Charlie";"Bravo",19,93,0.3,3,0,"Alpha";"Charlie",17,85,0.4,1,0,"Bravo";"Delta",13,67,0.276,4,0,"Delta"}

Note 1. You can put the COUNT part in the last formula in a cell of its own and the ref of this cell in the formula. Or, if you want to make a list of say 5 players with the highest averages, use this number (d.i. 5) instead of the COUNT part in the last formula.

Note 2. The last formula can be put on a different worksheet. You need then prefix the ranges with the name of the sheet where they are.

I believe this is what you really need.

Aladin

Aladin,

This is getting to what I want, especially lists

of the top-5, top-10, whatever.

However, I got lost in your "Note 1.", where you

say use "use this number (d.i. 5) instead of the

COUNT part in the last formula." What is (d.i. 5)?

I'm tracking five different minor league teams in

a club's farm system, and I'd like to be able to

produce lists of the top-10 players in home runs,

etc., among all those clubs instead of doing

multiple filters or sorts.

Thanks,

Cliff

NOTE 1 WAS ABOUT WHETHER TO LIST EVERYBODY OR TOP N. I PICKED OUT 5 AS EXAMPLE. BTW, "D.I." IS LATIN FOR "IT IS."

THAT WOULDN'T BE TOO DIFFICULT. IF YOU NEED HELP, PROVIDE PERTINENT DATA AND THE QUESTIONS THAT YOU NEED ANSWERS FOR.

My apologies, but I am accustomed used to using i.e. for id est (that is). If I had to guess, I'd say that d.i.

is the German translation, das ist.

I'll extract a subset of my data and zip up the Excel 97 file, then post it on a website. You should understand that I'm

not grasping the logic behind the answers I see to many of the question on this site about using Index, Match, etc. Once

this falls into place, I'll be able to strike out on my own.

Thank you,

Cliff

Okay, I've put up a small workbook at

http://members.home.net/bb-etc/cliff.zip

that has three sheets: Notes, ActualData, Workdata

if you'd like to take a look.

Thank you,

Cliff

You're right about i.e. Mea culpa. The one that I used is Dutch. I don't understand how this is slipped into my English, even when you questioned it. I guess it is one of those "cognitive accidents" I'll remember for a long time.

Aladin

That's okay. I can't tell from your writing that English isn't your tongue.

Cliff