Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Vlookup Duplicate Matches in Excel (5 Easy Ways)

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. ## 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. ### 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)
•  OutputNew 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 the value argument.
• OutputFALSE.
• 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. ### 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.
• 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”. ### 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. ## 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.

## Related Articles #### Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts 