Filter formula multi-criteria with exclude.

[gwaitsi]

Please help with a formula to do the following;
I want to know the Types that have "Orange" or "Red" and no other colors.
e.g. the correct answer is B, C, E
A, D and F should be excluded because they have more than just "Orange" i.e. they have "Blue" as well.

[TABLE="width: 128"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Color
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Blue
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Blue
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]Red
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]Blue
[/TD]
[/TR]
</tbody>[/TABLE]

Appreciate the help

 

[bosco_yip]

1] Assume your data incl. heading housed in A1:B10

2] In D2, formula copied down :

=IFERROR(INDEX(A$2:A$10,AGGREGATE(15,6,ROW(A$2:A$10)-ROW(A$1)/((COUNTIF(A$2:A$10,A$2:A$10)=1)*((B$2:B$10="Orange")+(B$2:B$10="Red"))=1),ROWS($1:1))),"")

Regards
Bosco

 

[gwaitsi]

Hi Bosco
Your assumptions are correct. But for some reason, it only detects C & E, not B

cheers