Microsoft Excel Data separation: 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.

Answer 1

let's say that your january list is in column A (starting A1, A2, etc) and July list is in B (B1, B2, etc).

in C1 enter

=VLOOKUP(A1, $B$1:$B$1000, 1, FALSE)

this tells excel to lookup the name in A1 and try and find a match in B1 thru B1000 and if it finds a match return the name from column B.

then copy/paste this all the way down as far as you have names in column A

if it's doesn't find a match it'll return "#NA"

so if anyone with an "NA" next to their name it means they were in the Jan list but not in the July list.

of course you'll need to change the cell references I used to match up to your different sheets/tabs/files/cell ranges