Cross referencing data- HELP!

[drinkonthemoon]

I'm conducting a report, where I have a list of registered students (over 10,000) with their student numbers and date of births. I have been given another list (of 6,000 students who all hold a certain type of card). I need to check that out of the 6000 students, that they are still registered with the school (and thus still able to have the card) by cross-referencing their student numbers.

How can I do this without "finding" them all one by one and marking them as still valid?

Thanks!

 

[BGY23]

Assuming you list of 10,000 students is in tab1 with student numbers in C2:C10002 and your list of 6,000 students is in tab 2 student numbers in D2:D6002.

On tab 2 in a convenient empty column i.e F. in F2 type =vlookup(F2,Tab1!$c$2:$C$10002,1,0) and copy down the 6000 rows. If the student number in F2 is in the list in tab 1 it will return the same number if not you get #N/A.

You could tidy it up with =if(iserror(vlookup(F2,Tab1!$c$2:$C$10002,1,0)),"No","Yes") this will show YES and NO for in the list or not in the list of 10,000.

Things to watch out for, 1 The format and syntax of both sets of numbers MUST be the same. 2. It only tells you that the student number exists in the 10,000 list not that its the same student. So if the same number can be issued to more than one student you're in trouble. 3 There should not be duplicate student numbers in either list.

Good Luck