Try this...

Defined names:

ID refers to: =$A$2:$A$5
Color refers to: =$B:$B

D2 = lookup ID = 2

Enter this formula in E2. This will return the count of lookup ID's.

=COUNTIF(ID,D2)

Enter this array formula** in D3 and copy down until you get blanks:

=IF(ROWS(D$3:D3)E$2,"",INDEX(Color,SMALL(IF(ID=D$ 2,ROW(ID)),ROWS(D$3:D3))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"CBI FlexCard" wrote in message
...
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