|
Looking up values,problem: they occure multiple times
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 | +-------------------------------------------------------------------+ -- Wingman ------------------------------------------------------------------------ Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602 View this thread: http://www.excelforum.com/showthread...hreadid=533798 |
Looking up values,problem: they occure multiple times
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" wrote in message ... 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 | +-------------------------------------------------------------------+ -- Wingman ------------------------------------------------------------------------ Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602 View this thread: http://www.excelforum.com/showthread...hreadid=533798 |
Looking up values,problem: they occure multiple times
Works perfectly fine, thank you very much! :) -- Wingman ------------------------------------------------------------------------ Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602 View this thread: http://www.excelforum.com/showthread...hreadid=533798 |
Looking up values,problem: they occure multiple times
You're welcome. Thanks for the feedback!
Biff "Wingman" wrote in message ... Works perfectly fine, thank you very much! :) -- Wingman ------------------------------------------------------------------------ Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602 View this thread: http://www.excelforum.com/showthread...hreadid=533798 |
| All times are GMT +1. The time now is 03:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com