Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |