software

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: www.excelforum.com/attachment.php?postid=4651 |
-------------------------------------------------------------------

--
Wingman
------------------------------------------------------------------------
Wingman's Profile: www.excelforum.com/member.php...oamp;userid=33602
View this thread: www.excelforum.com/showthread...hreadid=533798Hi!

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

quot;Wingmanquot; gt; wrote in
message ...
gt;
gt; I have a sheet, col A has values 1,2,3,.. occuring multiple times.
gt; Col B has a serialnumber assigned to each of those values in Col A.
gt;
gt; I'm making a list, that should put out the Serial (Col B) for the
gt; largest,2nd largest,.. value in Col A.
gt;
gt; I did LARGE(A:A,1),LARGE(A:A,2) to get the number,put them in Col E
gt;
gt; And INDEX(B:B,MATCH(E2,A:A,0),0) to read the value for that number.
gt;
gt; However as the number occur several times, it doesn't read the correct
gt; numbers.
gt;
gt; In the sheed I added: 1st,2nd and 3rd largest number is always 3, if
gt; thats the case, I would like excel to put out a different number for
gt; the 2nd and 3rd biggest value. (going from top to bottom)
gt;
gt; How would I be doing that?
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Test.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4651 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Wingman
gt; ------------------------------------------------------------------------
gt; Wingman's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33602
gt; View this thread: www.excelforum.com/showthread...hreadid=533798
gt;

Works perfectly fine, thank you very much! --
Wingman
------------------------------------------------------------------------
Wingman's Profile: www.excelforum.com/member.php...oamp;userid=33602
View this thread: www.excelforum.com/showthread...hreadid=533798You're welcome. Thanks for the feedback!

Biff

quot;Wingmanquot; gt; wrote in
message ...
gt;
gt; Works perfectly fine, thank you very much!
gt;
gt;
gt; --
gt; Wingman
gt; ------------------------------------------------------------------------
gt; Wingman's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33602
gt; View this thread: www.excelforum.com/showthread...hreadid=533798
gt;