While working in Excel, you might need to **find duplicates** in two different workbooks for identifying, modifying, or even** removing those duplicates.** In this article, I’m going to show you 5 methods on how to find duplicates in two different Excel workbooks with proper explanation. So that you may adjust the methods to your dataset.

**Table of Contents**hide

## Download Practice Workbook

## 5 Methods to Find Duplicates in Two Different Excel Workbooks

Let’s assume, we have two different datasets with a common field (**Employee Name**) in two different sheets and certainly in two workbooks. The first dataset is in **Sheet1 **of the **Employee Information1.xlsm** workbook (*Workbook1*).

The other one is in **Sheet1** of the **Employee Information2.xlsx** workbook (*Workbook2)* as shown in the following screenshot.

Now, you need to find the duplicates in those different worksheets.

Let’s check out the following methods.

### 1. Using the COUNTIF Function

In the beginning method, you’ll see the use of the **COUNTIF** function, one of the most popular functions, to count the number of cells with given criteria.

While counting duplicates, the generic formula will be like the following.

`=COUNTIF(Range, Criteria)`

In the case of finding duplicates in two different workbooks, the adjusted formula will be-

`=COUNTIF('[Employee Information2.xlsx]Sheet1'!$B$5:$B$15,B5)`

Here, **‘[Employee Information2.xlsx]Sheet1’!$B$5:$B$15 **is the range where I want to count duplicates. Needless to say that the **‘[Employee Information2.xlsx]Sheet1’ **represents the *Sheet1 *of *Workbook2* and **B5 **is the specific cell that I want to count in cells **B5 **through **B15 **at the *Workbook2 *(** criteria**).

After inserting the formula, press the **ENTER **key. And you’ll get the following output if you use the **Fill Handle Tool**.

If you look closely at the above output, you’ll find that the value of duplicates is **1**. On the other hand, the value of unique records is **0**.

**Read More:** **Finding out the number of duplicate rows using COUNTIF formula**

### 2. Using IF and COUNTIF Functions

Instead of getting the numerical value, you also can find the string “*Duplicates*” or “*Unique*” if you want. For accomplishing this, you have to assign the logical **IF** function with the previous function.

`=IF(COUNTIF('[Employee Information2.xlsx]Sheet1'!$B$5:$B$15,B5:B15),"Duplicate","Unique")`

⧭ If the value of the output using the **COUNTIF **function is greater than **0**, the **IF **function will return “*Duplicates*”. Else it’ll return “*Unique*”.

*Note:** here, I inserted the criteria as a range B5:B15. In such a situation, you don’t need to copy the formula for the below cells. Rather, you’ll get the output by pressing ENTER only.*

**Read More:** **Excel Formula to Find Duplicates in One Column**

### 3. Applying the VLOOKUP Function to Find Duplicates in Two Workbooks

The **VLOOKUP** function together with the **IF **and **IFERROR** function may be used to get the duplicates in two different Excel workbooks.

`=IF(ISERROR(VLOOKUP(B5,'[Employee Information2.xlsx]Sheet1'!$B$5:$B$15,1,0)),"Unique", "Duplicate")`

Here, **B5 **is the starting cell of *Workbook1*. **1** is the ** col_index_num** argument and

**0**is for approximate matching.

⧭ In the above formula, the **VLOOKUP **function returns the *Employee Name* if it finds similar in *Workbook2 *(duplicates). Else, it returns the** #N/A** error. Therefore, the **ISERROR **function is used to avoid any display of errors. Finally, the **IF **function provides the output as “*Duplicates*” or “*Unique*”.

Hit **ENTER** and drag down the formula. Shortly, you’ll get the following output.

**Read More:** **How to Vlookup Duplicate Matches in Excel (5 Easy Ways)**

**Similar Readings**

**How to Compare Two Excel Sheets Duplicates (4 Quick Ways)****How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)****Excel Find Duplicates in Column and Delete Row (4 Quick Ways)****How to Find Duplicate Rows in Excel (5 Quick Ways)****How to Find Duplicates without Deleting in Excel (7 Methods)**

### 4. Utilizing ISNUMBER and MATCH Functions

Furthermore, the **ISNUMBER** function combined with the **MATCH** function can be used to show duplicates. The combined formula is-

`=ISNUMBER(MATCH(B5,'[Employee Information2.xlsx]Sheet1'!$B$5:$B$15,0))`

⧭ In this formula, the **MATCH **function finds the relative position in the numeric value e.g. **4** for the **B8 **cell. of the matched lookup value. Otherwise, it will return the** #N/A** error. Lastly, the **ISNUMBER **function is used to display **TRUE **instead of showing the number and **FALSE **for the** #N/A** error.

So, the output will be as follows.

Here, **TRUE **represents “*Duplicates*” and **FALSE **represents “*Unique*” records.

**Read More:** **How to Find Duplicate Values in Excel Using Formula (9 Methods)**

### 5. Using the VBA Code to Find Duplicates in Two Excel Workbooks

Besides, you might use the **VBA **code to find the duplicates in two different workbooks as well as to highlight the duplicate values.

So, you have to create a module to use the** VBA** Code.

Firstly, open a module by clicking **Developer **> **Visual** **Basic**.

Secondly, go to **Insert **> **Module**.

Then, copy the following code into the newly created module.

```
Sub Duplicates_Workbooks_VBA()
Dim RngWorkbook1 As Range, RngWorkbook2 As Range, Rn1 As Range, Rn2 As Range
Set RngWorkbook1 = Application.InputBox("Range1:", "Insert Cell Range", Type:=8)
Set RngWorkbook2 = Application.InputBox("Range2:", "Insert Cell Range", Type:=8)
For Each Rn1 In RngWorkbook1
Rn1Value = Rn1.Value
For Each Rn2 In RngWorkbook2
If Rn1Value = Rn2.Value Then
Rn1.Interior.Color = VBA.RGB(255, 255, 0)
Exit For
End If
Next
Next
End Sub
```

⧭ In the above code, I declared necessary variables first. Then, I utilized the **InputBox** to insert the cell range for *Workbook1 *and *Workbook2 *respectively. Subsequently, I ran the **For** loop to find the duplicates in those workbooks. Lastly, I assigned the VBA **RGB** function to highlight the color. Specifically, I used **255 **as the value of the ** red **and

**arguments and**

*green***0**for the

**argument to highlight duplicates values in yellow color.**

*blue*Next, if you run the code (the keyboard shortcut is **F5 **or **Fn** + **F5**), you’ll get the input box to insert the cell range of *Workbook1*.

After pressing **OK**, you’ll get another input box immediately to insert the cell range of *Workbook2*.

Shortly, you’ll get the **highlighted duplicates** values as shown in the following screenshot.

**Note:** If you want to highlight duplicate values along with two workbooks, this method will be highly beneficial for you. Because the **Conditional Formatting** tool doesn’t work for two workbooks. Rather it is handly for highlighting duplicates across multiple sheets in a workbook.

**Read More:** **How to Find Duplicates in Excel Workbook (4 Methods)**

## Conclusion

That’s the end of today’s session. I strongly believe that from now you may find duplicates in two different Excel workbooks. Anyway, if you have any queries or recommendations, please share them in the comments section below.

**Related Articles**

**How to Find Matching Values in Two Worksheets in Excel (4 Methods)****Find Matches or Duplicate Values in Excel (8 Ways)****Excel Find Similar Text in Two Columns (3 Ways)****How to Compare Rows in Excel for Duplicates****Find Duplicates in Two Columns in Excel (6 Suitable Approaches)****How to Find, Highlight & Remove Duplicates in Excel**