Multiple workbook comparison

New Contributor

Is it possible to compare a list of names in two different workbooks in order to determine if names appear in one or both of them and then highlight all those that are found.  I'm new to excel and especially formulas so any help would be greatly appreciated.  

2 Replies


Highlighting is problematic, for a conditional formatting rule cannot refer to another workbook.

You could use a formula though.

Open both workbooks.

Let's say the names are in A2:A100 on the active sheet, and in B2:B60 on Data Sheet in OtherWorkbook.xlsx.

Enter the following formula in B2 on the active sheet, then fill down to B100:


=ISNUMBER(MATCH(A2,'[OtherWorkbook.xlsx]Data Sheet'!$B$2:$B$60,0))


Now first close OtherWorkbook.xlsx. Excel will add the path of that workbook to the formulas.

Then save the workbook with the formulas.


You can use column B to filter and sort the data.

I will give it a try, Thank you, Your help is greatly appreciated.