Finding the top n elements in a range

[pkd]

Hi,

I need to find top/bottom N elements in a range. I have created an array out of the range. I am not sure how to find the top/bottom elements? LARGE function would give me the actual value, however I am interested in finding the indexes. Please help

 

[Gerald Higgins]

Hi, welcome to the board.

Why not try the INDEX and LARGE functions together, possibly with the assistance of the MATCH function ?

 

[pkd]

Can you provide an example?

Lets say I have a range A1:A100, and I have created an array out of it. My array is integerArray(1 to 100, 1 to 1). Now, I need to find the top 3 elements in this array, and their indexes. Where do I go from here (without doing a for loop)?

 

[Gerald Higgins]

So long as all your values are unique . . .

=LARGE(A1:A100,3)

will return the 3rd largest value.

And this

=match(large(a1:a100,3),a1:a100,0)

will return its position in the array.