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


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.


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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question