With the query ID in Cell C1 enter the below array formula in cell C2 and
copy down as required

Col A Col B Col C
ID Color 2
1 Red =formula
2 Blue
2 Green
3 Black


=IF(COUNTIF($A$1:$A$100,C1)<ROW(A1),"",INDEX(B$1:B $100,
SMALL(IF($A$1:$A$100=C1,ROW($A$1:$A$100)),ROW(A1)) ))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"


If this post helps click Yes
---------------
Jacob Skaria


"CBI FlexCard" wrote:

I have an array with the lead column having duplicate ID #'s, but unique
related data in the array. I need to lookup all related data to the ID. In
the following example, I need to lookup ID #2 and find both Blue and Green.
Is this possible?

ID Color
1 Red
2 Blue
2 Green
3 Black

--
Bryan