Looking up values,problem: they occure multiple times

[Wingman]

I have a sheet, col A has values 1,2,3,.. occuring multiple times.
Col B has a serialnumber assigned to each of those values in Col A.

I'm making a list, that should put out the Serial (Col B) for the
largest,2nd largest,.. value in Col A.

I did LARGE(A:A,1),LARGE(A:A,2) to get the number,put them in Col E

And INDEX(B:B,MATCH(E2,A:A,0),0) to read the value for that number.

However as the number occur several times, it doesn't read the correct
numbers.

In the sheed I added: 1st,2nd and 3rd largest number is always 3, if
thats the case, I would like excel to put out a different number for
the 2nd and 3rd biggest value. (going from top to bottom)

How would I be doing that?


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4651 |
+-------------------------------------------------------------------+

 

[Biff]

Hi!

Use a helper column to rank the values in column A:

Enter this formula in C2 and copy down to C43:

=RANK(A2,A$2:A$43)+COUNTIF(A$2:A2,A2)-1

Enter this formula in G2 and copy down to G5:

=INDEX(B:B,MATCH(SMALL(C:C,ROWS($1:1)),C:C,0))

Biff

 

[Wingman]

Works perfectly fine, thank you very much! :

 

[Biff]

You're welcome. Thanks for the feedback!

Biff