# Help Please - Lookup in an array that has duplicte values

C

#### 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

J

#### 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

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

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