Excel Data Separation: -PLS READ INSIDE-Jan Roster + July Roster, how to get jan only members? ?

Question

I have 2 lists. A January Roster and a July Roster. I need to know the members that were on January Roster but who have left the July Roster. (The entry in Jan roster may be a15 but in July a24)

Answer 1

If you want to check if the names in one list is on another list, use match function

Say the January list is A1:A500, July list is C1:C400

To check every name in January list put this formula in B1, then copy it down to B500

=MATCH(A1, C$1:C$400,0)

if those cell in A1:A500 has a match (i.e. name on both list), a integer will show up otherwise, #N/A (only on January list)

Note: the integer show up is the row number of the table where the name matches