Finding the =LARGE() of a column and returning a previous column

[CM22]

I am trying to find the top ten values of column B and return their respective Titles from column A.

I am using this formula:
=OFFSET($B$8,MATCH(LARGE($B$8:$B$67,1),$B$8:$B$67,0)-1,-1)
=OFFSET($B$8,MATCH(LARGE($B$8:$B$67,2),$B$8:$B$67,0)-1,-1)

It has been working for me except when the values in column B are the same, e.g. each is 31 it only returns the top Title in from column A.

How should I get this to find the nth largest value in column B and return the corresponding value in column A?

Thanks

 

[Mike H..]

I am trying to find the top ten values of column B and return their respective Titles from column A.

I am using this formula:
=OFFSET($B$8,MATCH(LARGE($B$8:$B$67,1),$B$8:$B$67,0)-1,-1)
=OFFSET($B$8,MATCH(LARGE($B$8:$B$67,2),$B$8:$B$67,0)-1,-1)

It has been working for me except when the values in column B are the same, e.g. each is 31 it only returns the top Title in from column A.

How should I get this to find the nth largest value in column B and return the corresponding value in column A?

Thanks

Hi,

One way could be to create a helper column to break ties and then use this helper column to look up. For example you could put this in C8 and drag down to C67.

=B8+ROW()/100000000

I would then use a different formula to return column A like this.

=INDEX($A$8:$A$67,MATCH(LARGE($C$8:$C$67,ROW(A1)),$C$8:$C$67,0))

Drag this formula down.

 

[XLPadawan]

CM22,
First off you have to do something to ensure that none of the ordering numbers are equal. I achieve this by incorporating the row # of each ordering number into a calculation that appends a decimal # between 0 and 1 to the original ordering number. Each ordering number has a unique row # associated with it, making every new ordering # unique. Just look at what I've done to see how it works (use Evaluate Formula in the Formulas tab to step through the calculations):

 

[CM22]

Great, thank you both; I used the ROW() helper column.

I haven't been able to find a more elegant solution to the ranking problem, but I will keep looking.

Thanks again.