Formula to generate a Unique list of Missing values by comparing two lists

[Benders]

Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this

List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6

 

[Canapone]

Hi,

in A2:A10 first list, in B2:B5 second list.


Array INDEX in C2 :

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(COUNTIF($B$2:$B$5,$A$2:$A$10)=0,ROW($A$2:$A$10)-1),ROWS($A$1:$A1))),"")

To be confirmed with control + shift+enter and copied down.

Hope it helps

 

[NBVC]

Assuming data is in columns A:B, then in C2:

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(ISNA(MATCH($A$2:$A$5,$B$2:$B$10,0)),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($C$2:$C2))),"")

adjust ranges to suit, don't use ranges large than necessary to get all data. Then confirm with CTRL+SHIFT+ENTER not just ENTER and copy down.

 

[Benders]

My Respect and Thanks. The world again seems a nice place on a Friday