Help Please - Lookup in an array that has duplicte values
 

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

Help Please - Lookup in an array that has duplicte values
 

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

Help Please - Lookup in an array that has duplicte values
 

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