Cmpare Two Lists

[Excel777]

Hello all
i have two lists list 1 and list 2 i want to get results in two columns.

column 1 results give me the values that is found in list 1 and not found in list 2
column 2 results give me the values that is found in list 2 and not found in list 1
i wish the answer to be with one formula with out helper columns
thanks

 

[etaf]

EDIT
IGNORE not available in 2019 version

column 1 results give me the values that is found in list 1 and not found in list 2

=FILTER(A2:A9,COUNTIF(B2:B9,A2:A9)=0)

column 2 results give me the values that is found in list 2 and not found in list 1

=FILTER(B2:B9,COUNTIF(A2:A9,B2:B9)=0)

I think those functions are available in 2019, but may be only available in version 365, EDIT - should have looked up first , as it appears only in 365 versions
So sorry about that

Book10
	A	B	C	D	E
1	List1	List2		In List1, Not 2	inlist2 not 1
2	z	a		z	12
3	b	12		d	2
4	c	2		X	14
5	d	14		f	3
6	X	3
7	f	5
8	5	c
9	a	b
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(A2:A9,COUNTIF(B2:B9,A2:A9)=0)
E2:E5	E2	=FILTER(B2:B9,COUNTIF(A2:A9,B2:B9)=0)
Dynamic array formulas.

I will be seeing family for a few days, back on 30th Dec

So i may not reply now until then - sorry if i have misunderstood - or leave hanging

Hopefully other members will pickup before

 

[Excel777]

etaf thank you very much it works and merry Christmas

 

[etaf]

oh,
i did edit, as i have looked up and FILTER() is not available in 2019 - according to the web
Do you have 2019 version or 365
perhaps the info is out of date on the web

anyway
you are welcome