Help Please - Lookup in an array that has duplicte values

[CBI FlexCard]

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

 

[Jacob Skaria]

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

 

[T. Valko]

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$33)>E$2,"",INDEX(Color,SMALL(IF(ID=D$2,ROW(ID)),ROWS(D$33))))

** 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.