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.

Overview of the methods to find duplicate matches using 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.

First sample dataset


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.

Using VLOOKUP function find duplicate matches in Excel

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

Using Fill Handle to copy down the formula

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

Outputs obtained after using VLOOKUP function find duplicate matches in Excel

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.

Applying MATCH function find duplicate matches Excel

  • Finally, use the Fill Handle tool to copy the formula.

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

Using Fill Handle to get the remaining outputs

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)
    •  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.

Combining IF, ISNA, VLOOKUP Functions to Find Duplicate Matches

  • Finally, use the Fill Handle tool to copy the formula.

Outputs got after using Fill Handle in Excel


Similar Readings


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.

Finding Duplicate Values in Two Columns Using IF, ISNA, VLOOKUP Functions

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

Output obtained after using the IF, ISNA, VLOOKUP Functions

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.

Second Sample Dataset

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.

Using COUNTIF function to find the Unique IDs

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

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

Using Fill Handle to get the remaining Unique IDs

  • 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.

Using VLOOKUP And COUNTIF Functions to Find Duplicate Matches in Excel

  • 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.

Final output got after using VLOOKUP And COUNTIF Functions

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.

Sample practice section


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

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

Leave a reply

ExcelDemy
Logo