| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
| view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) | |||
| How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
| Adding multiple worksheets | Excel Worksheet Functions | |||
| Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
Linear Mode
