Results that match and do not match

Question

Example Sheet link
I am trying to create a formula that pulls two separate lists:
The first list needs to be people from List2 that ARE NOT on List1.
The second list needs to be people from List2 that ARE ALSO on List1. 
I keep thinking maybe I'm using the entirely wrong formulas to obtain the results I am looking for or that I'm missing something super simple. 
Thank you so much for your help.

James/mreighties · Accepted Answer

Hello, I made a copy of your sheet instead of duplicating your tab sheets... https://docs.google.com/spreadsheets/d/15BzdrHnI8Gd6yjVsKn9LlHvlBuSxbsBNu0QwJiZ9W2g/edit?usp=sharing ...you can see the formulas I used in the yellow cells... The first list needs to be people from List2 that ARE NOT on List1. =filter({A2:A,B2:B},B2:B<>"",isna(match(B2:B,List1!B2:B,0))) The second list needs to be people from List2 that ARE ALSO on List1. =filter({A2:A,B2:B},B2:B<>"",match(B2:B,List1!B2:B,0)) Hope this helps, James :)

Matt:King · Accepted Answer

Hi Yvonne,
 
for matching problems like this, it's useful to use the combination of ISNUMBER() and MATCH() or ISNA() and match. 
 
So for the ones that are not on the list...
 
=ARRAYFORMULA(FILTER(A2:B,ISNA(MATCH(A2:A,List1!A2:A,0))))
 
And the ones that are....
 
=ARRAYFORMULA(FILTER(A2:B,ISNUMBER(MATCH(A2:A,List1!A2:A,0))))
 
You can see these two on the MK.Help tab that I made.
Hope this helps!
 
Matt