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

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