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
|