Md. Sourov Hossain Mithun
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.
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 the value argument.
- Output → FALSE.
- 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.
- 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.
