Function needed?

[Annie]

I need to learn how to compare two spreadsheets in Excel to identify
differences between the two. For example, if I have one spreadsheet with a
list of staff and staff numbers, and a different spreadsheet listing staff
and staff numbers, I need to be able to compare the two to identify any
employees whose staff number differs between the two. I am a new user so I
do not know which function to try - someone said match???

Many thanks

 

[Jacob Skaria]

Suppose you have 2 unsaved workbooks. Book1 and Book2 .....(If the books are
saved modify the formula to include the extension as Book2.xls)

Contents of Book1 Sheet1 . Column C is the formula column..In cell C2 apply
the below formula and copy down as required

=IF(ISERROR(VLOOKUP(A2,[Book2]Sheet1!$A:$B,2,0)),"Number not found",
IF(VLOOKUP(A2,[Book2]Sheet1!$A:$B,2,0)<>B2,"Mismatch","Same"))


Col A Col B Col C
Number Name Result
1001 Jack Same
1002 Annie Number not found
1003 John Same
1004 Sally Mismatch

Book2 Sheet1 contents
Col A Col B
Number Name
1001 Jack
1004 Annie
1003 John