Forum Discussion
Large/small Formula to identify the nth duplicate and return the row number
I am trying to get the specific row number of the largest or smallest value in order and unfortunately cannot seem to find a solution that gives the row number. =MATCH(LARGE(IF(N1:N2000<>0,N1:N20...
=IFERROR(MATCH(LARGE(IF((N$1:N$2000<>0)*(COUNTIF(N$1:N$2000,N$1:N$2000)>1),ROW($1:$2000)),ROW(1:1)),ROW($1:$2000),0),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
OliverScheurich Thank you for your response, unfortunately, the formula appears to take the values <= 2,000 from the last number in the column and work sequentially up, as opposed to in order of the greatest number.
This was the result vs expectation :-
Hopefully, this further clarifies what I am attempting to achieve.
Which formula do you use in your sheet?
The suggested formula returns the expected result. I entered 11000 in cells N7 and N240 and the formula returns 240 and 7.
- Hello, I used the one you suggested - =IFERROR(MATCH(LARGE(IF((N$1:N$2000<>0)*(COUNTIF(N$1:N$2000,N$1:N$2000)>1),ROW($1:$2000)),ROW(1:1)),ROW($1:$2000),0),"")
Can you attach your workbook without sensitive data?