Index & Match
 

Hello,

I'm using the following formula but am running into an issue.

INDEX(Data!$A$12:$A$1314,MATCH(LARGE(OFFSET(Data!$ T$12:$T$1314,0,0),1),OFFSET(Data!$T$12:$T$1314,0,0 ),0))

When trying to find the n-th largest value multiple entries have the same
value so they are all the n-th largest value. Therefore the list comes up
with one name being entered as, for example, the 4th, 5th, and 6th largest.
Any suggestions on how to fix this?

Thank you!

Index & Match
 

Stan,

You will need to have a column of formulas that breaks the ties by adding some small amount based on
another criteria - I often will use a formula like

=T12+ROW()/100000000
or
=T12 +0.1/ROW()

Then use those tie-broken values in your formula.

Note that the constant values used will depend on the actual values in the data set - you need to
add an amount smaller than the smallest delta in your data set.

HTH,
Bernie
MS Excel MVP


"stan" wrote in message
...
Hello,

I'm using the following formula but am running into an issue.

INDEX(Data!$A$12:$A$1314,MATCH(LARGE(OFFSET(Data!$ T$12:$T$1314,0,0),1),OFFSET(Data!$T$12:$T$1314,0,0 ),0))

When trying to find the n-th largest value multiple entries have the same
value so they are all the n-th largest value. Therefore the list comes up
with one name being entered as, for example, the 4th, 5th, and 6th largest.
Any suggestions on how to fix this?

Thank you!