Help with filter / compare two lists

[Dazzybeeguy]

Hi - I want to compare the New List to the Old to Identify any new people. I want to then extract a list of all the new people to include the Ref and Name from Columns E & F.

Not sure how to do it all. Any help / guidance would be gratefully appreciated.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Old List			New List			H2 Formula =XLOOKUP(E2:E13,B2:B13,C2:C13,"New Person",0)		J2 Formula is =FILTER(H2#,(H2#="New Person"))
2		A12345	Arthur		A12345	Arthur		Arthur		New Person
3		A12346	Brian		A12346	Brian		Brian		New Person
4		A12347	Cathy		A12347	Cathy		Cathy		New Person
5		A12348	Dina		A12348	Dina		Dina		New Person
6		A12349	Ellis		A12349	Ellis		Ellis
7		A12350	Fiona		A12350	Fiona		Fiona
8		A12351	Gary		A12351	Gary		Gary
9		A12352	Harold		A12352	Harold		Harold
10		A12353	Isla		A12353	Isla		Isla
11		A12354	John		A12354	John		John
12		A12355	Kevin		A12355	Kevin		Kevin
13		A12356	Liam		A12356	Liam		Liam
14					A12357	Mike		New Person
15					A12358	Niah		New Person
16					A12359	Orenthal		New Person
17					A999999	Zac		New Person
18
19
Sheet1

Cell Formulas
Range	Formula
H2:H17	H2	=XLOOKUP(E2:E17,B2:B13,C2:C13,"New Person",0)
J2:J5	J2	=FILTER(H2#,(H2#="New Person"))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H13	Cell Value	="New Person"	text	NO

 

[Dazzybeeguy]

I have found a solution

i.e. =FILTER(E2:H17,H2#="new person")

Is there a better solution?

 

[SunnyAlv]

try

=FILTER(F2:F17,IFERROR(XMATCH(E2:E17,B2:B13,0),"New")="New")

 

[MARZIOTULLIO]

Book1
	B	C	D	E	F	G	H	I
1	Old List			New List
2	A12345	Arthur		A12345	Arthur		A12357	Mike
3	A12346	Brian		A12346	Brian		A12358	Niah
4	A12347	Cathy		A12347	Cathy		A12359	Orenthal
5	A12348	Dina		A12348	Dina		A999999	Zac
6	A12349	Ellis		A12349	Ellis
7	A12350	Fiona		A12350	Fiona
8	A12351	Gary		A12351	Gary
9	A12352	Harold		A12352	Harold
10	A12353	Isla		A12353	Isla
11	A12354	John		A12354	John
12	A12355	Kevin		A12355	Kevin
13	A12356	Liam		A12356	Liam
14				A12357	Mike
15				A12358	Niah
16				A12359	Orenthal
17				A999999	Zac
Sheet1

Cell Formulas
Range	Formula
H2:I5	H2	=FILTER(E2:E17,ISNA(MATCH(E2:E17,B2:B13,0)))
Dynamic array formulas.

H2=FILTER(E2:E17,ISNA(MATCH(E2:E17,B2:B13,0)))

copy across

 

[Peter_SSs]

.. or without copying across, just include both columns to start with.

24 12 12.xlsm
	B	C	D	E	F	G	H	I	J	K
1	Old List			New List					New People
2	A12345	Arthur		A12345	Arthur				A12357	Mike
3	A12346	Brian		A12346	Brian				A12358	Niah
4	A12347	Cathy		A12347	Cathy				A12359	Orenthal
5	A12348	Dina		A12348	Dina				A999999	Zac
6	A12349	Ellis		A12349	Ellis
7	A12350	Fiona		A12350	Fiona
8	A12351	Gary		A12351	Gary
9	A12352	Harold		A12352	Harold
10	A12353	Isla		A12353	Isla
11	A12354	John		A12354	John
12	A12355	Kevin		A12355	Kevin
13	A12356	Liam		A12356	Liam
14				A12357	Mike
15				A12358	Niah
16				A12359	Orenthal
17				A999999	Zac
New People

Cell Formulas
Range	Formula
J2:K5	J2	=FILTER(E2:F17,ISNA(MATCH(E2:E17,B2:B13,0)),"")
Dynamic array formulas.