How to find the LOCATION of the K'th largest value of a range in Excel?
The question is as in the title. Note that LARGE(range, k) doesn't tell me the location of the value, and attempting to search for it using INDEX can fail in the presence of duplicates. Is there another way?
Depending on the original data and whether you're in a position to modify the workbook, one way is to use a "helper column" to give you unique values by adding a very small row-dependent value to your data, eg the row number divided by 1000000. As long as the additional value isn't greater than the smallest true difference in data values it will not change the rank ordering of the data, but it does make it unique and has the benefit of being relatively "easy".
In this example B3:B17 contains mostly integer values, a couple of ".5" fractions, with some duplication. I have set C3:C17 as the helper column, in which I'm adding and adjustment value of the row number * 0.0000001. As the smallest step change in the genuine data is much larger than the largest adjustment value, there is no risk of incorrect ordering - only an imposition that where the original data is exactly the same, ordering is now based on location in the table.
Column F lists the largest helper values (eg cell F3 formula "=LARGE($C$3:$C$17,E3)"), column G finds the row that value is in (cell G3 formula "=MATCH(F3,C:C,0)"), and then your output table looks in the genuine data using this row number.
what if you use one column and type=IFERROR(RANG.eq(K2;"range of K")+SUMPRODUCT(--(K2=K$2:K$74"ex");--(K2<K$2:K$74));"") and one column =IFERROR(COUNTIFS(M2"new column":$M$74;M2)+M2-1;""). After that all numbers in K will be ranged even if equal and you can use LARGE