Compare two lists and only extract non-duplicated values from first list

[LeonardH]

Hello,

I have two lists that each include lists of options. Column A includes all options available. Column B includes selected options. I would like to return in Column C all options in Column A that were not selected in Column B. I would prefer some sort of dynamic formula that would scrunch the returned values so as to eliminate any blank cells.

The current formula I've inherited is

=IF(IF(ISERROR(MATCH(A3,$B$3:$B$200,0)),"Unique","Duplicate")="Unique",$A3,"")

A sample table of the desired result is below:

A​	B​	C​
Complete Options	Selected Options	Non-Selected Options
Acai	Acai	Banana
Banana	Cupuacu	Currant
Currant		Jamba
Cupuacu
Jamba

Thanks in advance!

 

[Bedlam]

Non Selected =UNIQUE(FILTER(FILTER(A2:A200,COUNTIF(B2:B200,A2:A200)=0),FILTER(A2:A200,COUNTIF(B2:B200,A2:A200)=0)<>0))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1	Complete Options	Selected Options	Non-Selected Options
2	Acai	Acai	Banana
3	Banana	Cupuacu	Currant
4	Currant		Jamba
5	Cupuacu
6	Jamba
Sheet6

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A100,(A2:A100<>"")*(ISNA(XMATCH(A2:A100,B2:B20))))
Dynamic array formulas.

 

[Bedlam]

@Fluff quit showing me up with simpler ways . Perhaps a tweak to Fluff's in case of nothing. =FILTER(A2:A200,A2:A200<>"")*ISNA(XMATCH(A2:A200,B2:B200))),"None Found")

 

[LeonardH]

Thanks @Bedlam and @Fluff. Both solutions worked! Fluff's reduced the fluff a bit which was helpful

 

[Fluff]

Glad we could help & thanks for the feedback.