How to fix equation to find top 2 highest values

Hi All -

I have the following 2 formulas that have helped me to determine the top 2 scores and return the respective headers, but they aren't perfect and I need help.

=INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))

• The above returns the max, however doesn't take into account scores that are a tie or 0 values

=INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))

• The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).

How can I fix both of these equations and also determine the 3rd highest score?

Your help would be appreciated!

Re: How to fix equation to find top 2 highest values

Hi -

This is close, but still doesn't solve for this:

[TABLE="width: 500"]

[tr]

[td]

John

[/td]

[td]

0

[/td]

[td]

0

[/td]

[td]

1

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

Sarah

[/td]

[td]

0

[/td]

[td]

1

[/td]

[td]

0

[/td]

[td]

1

[/td]

[/tr]

[tr]

[td]

Stacy

[/td]

[td]

2

[/td]

[td]

2

[/td]

[td]

0

[/td]

[td]

0

[/td]

[/tr]

[/TABLE]

For example - Sarah the highest value is 1, but the 2nd highest would be 0, but you have 2 columns which are both zero.

Quote from venkat1926;705394

see attached sheet1

B2 formkula
B3 formula
B3 is copied down

I do not understand how 0 comes here.

Re: How to fix equation to find top 2 highest values

Quote from venkat1926;705510

for sarah's 0 whlich one do you want the first one or the second one

Im hoping it will identify a tie and show both column headers. (for the 1st highest score)

Assuming 1 was the highest and lets say there was a tie for the 2nd highest score (if it were a 0) the 2nd highest score would be a N/A as 0 is not a score.