finds 2nd largest in one column and returns from different column but uses tie breakers?

[Kenja824]

I was trying to figure this out at home over the weekend. Every formula I tried to write would repeat the same name for the 2nd 3rd and 4th highest value due to the tie at 2nd highest. How do I write a formula that lists them due to tie breakers?

So in my list below, it should return...
pat
adam
mark
lucas
jake
tom

 

[SkipVought]

Hi,

You should always post an example that can you can COPY n PASTE. Otherwise, people won’t bother to reconstruct your picture.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

I attached it as well as copied and pasted it in the body to be copied and pasted to any excel sheet.

I didnt think about doing that as I dont have the original at work and I made this one so quick just for the image example. lol


tom 10 1300 Looks up largest value in C and returns same row in column A
bob 6 1200 Looks up 2nd largest value in C and returns same row in column A
pat 4 2700 Looks up 3rd largest value in C and returns same row in column A
mark 8 1800 Looks up 4th largest value in C and returns same row in column A
adam 12 1800 Looks up 5th largest value in C and returns same row in column A
jake 3 1400
lucas 7 1800 Tie Breaker would be highest value in B

 

[Kenja824]

Oh, once I post it the copied and pasted version changes to be unusable I guess. Well its still attached. lol

 

[SkipVought]

The simplest way would be to SORT your table...
[pre]
pat 4 2700
adam 12 1800
mark 8 1800
lucas 7 1800
jake 3 1400
tom 10 1300
bob 6 1200
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

The problem is I am looking to use the formula on another sheet to extract the top players in a category. So on a different sheet, I need it to give me the ten players who have had the biggest stats according to which category I select and if there is a tie, I need it to list them both or all three if there are three who tie.

I found something that is giving me ideas how to work it. Using INDEX, MAX, LARGE and possibly using a countif to break the ties. The countif would be an additional column I would add that each cell has an expanding range to only count those above it and not below it. Not sure exactly how to make it work yet but its forming. lol

 

[MintJulep]

Use INDEX() with RANK.EQ()

For your tiebreaker, simply add your base rank and your tiebreaker criteria and work with the sum. You might need to multiply or divide one or the other by 10 or 100.

 

[SkipVought]

Again, if your table were sorted then the first 10 are a simple INDEX(); third arg 1-10 for the 10 formulas.
[tt]
=INDEX(A:A,1,1)
=INDEX(A:A,2,1)
...
=INDEX(A:A,10,1)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

To be honest, I was confused on the use of Rank.EQ in this case. But the idea I had seems to work out. I had to add a column "Q" that used a countif and if the return was larger than 1, it took his attempts (column E) * .00001 and subtracted it from the yards in column G. This changes the number by a minute amount to give each one a different number and then the INDEX MATCH LARGE formula did its job.

The new file is attached with a better look at it. You probably see an easier way but unfortunately sorting the stats would cause too much a hassle for each category.

 

[Kenja824]

As usual, thanks for the help again.

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]