comparing two spread sheets

T

tim L

In spread sheet "A" and "B" the last names are in column B and first names are in column C. I want EXCEL to tell me if the names (a name consist of a last and first name) on spread sheet "B" or in spread sheet "A" spread sheet "A" may or may not already contain the names in spread sheet "B". How do you do this is EXCEL?
 
F

Frank Kabel

Hi
one way: enter the following array formula (entered with
CTRL+SHIFT+ENTER) in column D of spreasheet A:
=IF(ISNA(MATCH(1,('sheetB'!$B$1:$B$1000=B1)*('sheetB'!$C$1:$C$1000=C1),
0)),"not in sheet B","duplicate")
and copy this down
 
Top