Matching duplicates in Excel is very easy. We can use different functions and **formulas to find duplicates in Excel**. In this article, we will discuss the best and easy methods to find duplicate matches using the **VLOOKUP function** in Excel. Now, let’s start this article and explore these methods.

The following image demonstrates the overview of the methods that we have used in this article to find duplicate matches using the **VLOOKUP function** in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the free Excel template from here and practice on your own.

## 5 Effective Ways to Vlookup Duplicate Matches in Excel

Let’s get introduced to our dataset first. In my dataset, I have placed some salespersons’ names and their selling states for consecutive two weeks. Take a look that there are some states that are common. Now I will show how to find duplicate matches with **VLOOKUP** and other functions in Excel.

### 1. Using VLOOKUP Function to Find Duplicate Matches in Excel

In our very first method, we’ll use the** VLOOKUP function** to find duplicates. The **VLOOKUP function** can look up a value in the leftmost column of a data table and returns the corresponding value from another column located on the right side. Here, our lookup value will be from **Column D** and will find the duplicates from **Column C**. If a duplicate is found then it will show the state name. Otherwise, it will show **#N/A**.

**Steps:**

- Firstly, click on cell
**E5**and then type the formula given below.

`=VLOOKUP(D5,$C$5:$C$11,1,FALSE)`

- After that, just hit the
**ENTER**button to get the result.

- Later,
**double-click**the**Fill Handle**icon to copy the formula.

As a result, you will see that duplicates are found as shown in the following image.

**Read more:** **Find Duplicates in Two Columns in Excel**

### 2. Applying MATCH Function

Now we’ll find duplicates using the** MATCH function**. But here, our lookup value will be from **Column C** and will find the duplicates from **Column D. **If a duplicate is found then it will show the row number of the duplicate value, if not then it will show the **#N/A** error. Remember that here row number counted referred to the selected array.

**Steps:**

- Firstly, type the formula in cell
**E5.**

`=MATCH(C5,$D$5:$D$11,FALSE)`

- Following that, press the
**ENTER**button.

- Finally, use the
**Fill Handle**tool to copy the formula.

Consequently, you will spot that duplicates are extracted with their **array row number**.

**Read more:** **Find Matches or Duplicate Values in Excel**

### 3. Combining IF, ISNA, and VLOOKUP Functions to Find Duplicate Matches

Now we’ll combine three functions to match duplicates. Those are the **IF**, **ISNA**, and **VLOOKUP **functions. The **IF **function checks whether a condition is met and returns one value if true and another value if false. The **ISNA **function is an error handling function, It helps to find out whether any cell has the **#N/A** error or not. Here, we’ll match duplicates in **Column D** for the value of **Column C. **If a duplicate is found it will show “Duplicate”, otherwise it will show “Unique”.

**Steps:**

- Firstly, in
**Cell E5**write the following formula.

`=IF(ISNA(VLOOKUP(D5,$C$5:$C$11,1,FALSE)),"Unique","Duplicate")`

**Formula Breakdown**

- First, the VLOOKUP function will look up the
**Cell D5**to the array**C5:C11**.**VLOOKUP(D5,$C$5:$C$11,1,FALSE)**-
**Output**→**New York**.

- Now, the ISNA function will show FALSE as it doesn’t get any error if got, it would show TRUE.
**ISNA(VLOOKUP(D5,$C$5:$C$11,1,FALSE))**becomes →**ISNA(New York)**.- Here,
**New York →**This is theargument.**value** **Output**→**FALSE**.

- Here,
- Finally, the
**IF**function becomes,**IF(FALSE,”Unique”,”Duplicate”)****Output → Duplicate**.

- After that, just hit the
**ENTER**button.

- Finally, use the
**Fill Handle**tool to copy the formula.

**Similar Readings**

**How to Compare Rows in Excel for Duplicates****Excel Find Similar Text in Two Columns (3 Ways)****How to Find & Remove Duplicate Rows in Excel****Excel Find Duplicate Rows Based on Multiple Columns**

### 4. Finding Duplicate Values in Two Columns Using IF, ISNA, and VLOOKUP Functions

In this method, we’ll use the same previous methods’ functions to find duplicate matches in two columns. That’s why I have placed the lookup value in cell **D13**. Now we’ll use this cell reference to find the match of it in both columns** C** and **D**. If we find a match then it will show “Duplicated” otherwise “Unique”.

**Steps:**

- Firstly, write the given formula in cell
**D14**.

`=IF(IF(ISNA(VLOOKUP(D13,$C$5:$C$11,1,FALSE)),0,1)+IF(ISNA(VLOOKUP(D13,$D$5:$D$11,1,FALSE)),0,1)=2,"Duplicate","Unique")`

**Formula Breakdown**

- Here, the
**ISNA**and**LOOKUP**functions work like the previous method.**ISNA(VLOOKUP(D13,$C$5:$C$11,1,FALSE))****Output →****FALSE**.

- Now, the
**IF**function becomes**→****IF(FALSE,0,1)**- Then the
**IF**function will show**O**for**FALSE**and**1**for**TRUE**for the array**C5:C11**. **Output →****1**.

- Then the
- In the second
**IF**function, it will show**O**for**FALSE**and**1**for**TRUE**for the array**D5:D11**.**Output →****1**.

- Now, the final IF function becomes,
**IF(IF(ISNA(VLOOKUP(D13,$C$5:$C$11,1,FALSE)),0,1)****+IF(ISNA(VLOOKUP(D13,$D$5:$D$11,1,FALSE)),0,1)=2,”Duplicate”,”Unique”)****→ IF(1+1=2,”Duplicate”,”Unique”)**

- Now, the final
**IF**function will sum up the output of those two**IF**functions. If the sum returns**2**then it will show “**Duplicate”**, If not then will show “**Unique”**.**Output →****“Duplicate”**.

- Following that, click the
**ENTER**button for the output.

Subsequently, you will observe that it is showing **“Duplicate”**.

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

### 5. Using VLOOKUP and COUNTIF Functions to Find Duplicate Matches

For this method, I have made a new dataset for this method. I have used some programming language course names, their IDs, and participants’ names. You will see that some people have taken the same course. Now we’ll apply the **VLOOKUP** and **COUNTIF** functions together to match duplicates. We know that **VLOOKUP **always shows the first occurrence. What to do If we want the next occurrence values? Let’s see.

At first, we’ll create **Unique IDs** using the **COUNTIF **function.

**Steps:**

- Firstly, type the given formula in cell
**B5.**

`=COUNTIF($C$5:C5,C5)&"-"&C5`

- Then, hit the
**ENTER**button.

- After that, use the
**Fill Handle**tool to copy the formula.

Now, you will see that the duplicate IDs are in a serial number.

- Now, write the following formula in cell
**D15.**

`=VLOOKUP(COUNTIF($C$15:C15,C15)&"-"&C15,$B$5:$E$11,4,FALSE)`

**Formula Breakdown**

- The
**COUNTIF**function will count the occurrence number of Cell**C15**.**COUNTIF($C$15:C15,C15)****Output → 1**.

- After that, it will then add a hyphen and the value of the cell with the occurrence number to make a
**Unique ID**.**COUNTIF($C$15:C15,C15)&”-“&C15****Output → 1-C102**.

- Finally, the
**VLOOKUP**function will lookup according to that**Unique ID**to the array**B5:E11**and will show the output from**Column 4**of that array.**VLOOKUP(COUNTIF($C$15:C15,C15)&”-“&C15,$B$5:$E$11,4,FALSE) → VLOOKUP(“1-C102”,$B$5:$E$11,4,FALSE)**.**Output → “Peter”**.

- Following that, click the
**ENTER**button.

- Lastly, use the
**Fill Handle**tool to copy the formula.

As a result, you will see that we have got the next occurrence values that mean participant names for the same **Course ID**.

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

## Practice Section

In the Excel Workbook, we have provided a **Practice Section** on the right side of the worksheet. Please practice it yourself.

## Conclusion

I hope all of the methods described above will be good enough to find duplicate matches using the **VLOOKUP function** in Excel. Feel free to ask any questions in the comment section and please give me feedback. Furthermore, follow our website, **ExcelDemy**, to explore more about Excel-related problems.