Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up non-unique data
I haven't had to do this for so long that I've forgotten how, so memory jogs would be appreciated. Assume datalist subset: A1:A10 = 1,2,3,4,3,2,1,2,3,4 B1:B10 = a,a,a,b,b,b,c,c,c,d C1:C10 = v,w,x,y,z,v,w,x,y,z How can all corresponding values in B1:C10 be retrieved, in order, based on a selection in Col A? i.e. enter 3, and retrieve for printing: 3 a x 3 b z 3 c x Lookups? Index-Match? Arrays? Filtering? Long, messy IFs? anyone done this recently? thanks, david -- david1 ------------------------------------------------------------------------ david1's Profile: http://www.excelforum.com/member.php...fo&userid=1882 View this thread: http://www.excelforum.com/showthread...hreadid=503142 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up non-unique data
Assuming D1 contains your criterion, such as 3, try...
E1, copied down and across: =IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$10,$D$1),INDEX(A $1:A$10,SMALL(IF($A$1:$ A$10=$D$1,ROW($A$1:$A$10)-ROW($A$1)+1),ROWS(E$1:E1))),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , david1 wrote: I haven't had to do this for so long that I've forgotten how, so memory jogs would be appreciated. Assume datalist subset: A1:A10 = 1,2,3,4,3,2,1,2,3,4 B1:B10 = a,a,a,b,b,b,c,c,c,d C1:C10 = v,w,x,y,z,v,w,x,y,z How can all corresponding values in B1:C10 be retrieved, in order, based on a selection in Col A? i.e. enter 3, and retrieve for printing: 3 a x 3 b z 3 c x Lookups? Index-Match? Arrays? Filtering? Long, messy IFs? anyone done this recently? thanks, david |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count unique items in a pivottable data field. | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
How to validate data entries to be unique within an array | Excel Worksheet Functions |