×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

 Forum Search FAQs Links MVPs

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

(OP)
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
mark
lucas
jake
tom

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

Hi,

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

Skip,

Just traded in my OLD subtlety...
for a NUance!

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

(OP)
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

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

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


Skip,

Just traded in my OLD subtlety...
for a NUance!

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

(OP)
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

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

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.

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

Again, if your table were sorted then the first 10 are a simple INDEX(); third arg 1-10 for the 10 formulas.

=INDEX(A:A,1,1)
=INDEX(A:A,2,1)
...
=INDEX(A:A,10,1)

Skip,

Just traded in my OLD subtlety...
for a NUance!

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

(OP)
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.

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

Skip,

Just traded in my OLD subtlety...
for a NUance!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!