Vlookup for Duplicate and Return Matches in Excel [8 Cases]

To find the duplicate values in Excel, we need to Vlookup (Vertical Lookup) through columns. Sometimes we need just to identify the duplicate values. But sometimes we need to pull the duplicates out of the data table too. Different scenarios can arise regarding looking up duplicate values in Excel. In this article, I have discussed 8 relevant cases to vlookup for duplicate values and return the matches in Excel.

In the following picture, the Product List 1 and Product List 2 columns contain different product names. There are some common product names between the two columns. My target is to show you how can you find these matched product names.

Vlookup for Duplicate Based on Matches in Excel with VLOOKUP & IFERROR Functions

In this part, I will vlookup between the columns Product List 1 and Product List 2. Then I will extract the product names from the Product List 2 column that matches with the Product List 1 column contents.

For this purpose, I will use a formula using the VLOOKUP and IFERROR functions.

Syntax

=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])," ")

Formula

=IFERROR(VLOOKUP(D3,$C$3:$C$12,1,FALSE)," ")

Formula Breakdown

• D3 is the lookup value which is the top cell of the column Product List 2.
• $C$3:$C$12 is the table array which is the range of the column Product List 1.
• 1 is the column index number which refers to the first column in the range $C$3:$C$12.
• FALSE is responsible for finding the exact matches between Product List 1 and Product List 2.
• Here, the VLOOKUP function is used to vlookup and find duplicates. If the function leaves any error that replaces the IFERROR function with blanks (“”).

To vlookup for duplicate values to pull data based on matches using VLOOKUP & IFERROR Functions in Excel, follow these steps below:

1. Create a separate column to extract the duplicate values.
   Here, I’ve created a column named “Duplicate Products”.
2. Copy the following formula of IFERROR and VLOOKUP functions in the top cell of the new column.
3. Press ENTER to insert the formula.
   
4. Keep the mouse cursor at the right-bottom corner of the cell where you have just inserted the formula.
   A plus-like icon (+)  will appear.
5. To copy the formula down the entire column double-click on the icon.
   

Duplicate values from the Product List 2 column are extracted to the column “Duplicate Products”.

Vlookup for Duplicate in Excel & Return Matches with Multiple Functions

Here, I will compare the column Product List 2 with Product List 1. Then I will return the row number of the value from Product List 1 that matches with a value from Product List 2.

I will use a formula of IFERROR, CONCATENATE, SUM, & MATCH functions to perform the operation.

Syntax

=IFERROR(CONCATENATE("Matched with Row ",SUM(MATCH(lookup_value, lookup_array, [match_type]),adjust_num)),"")

Formula

=IFERROR(CONCATENATE("Matched with Row ",SUM(MATCH(D3,$C$3:$C$12,FALSE),2)),"")

Formula Breakdown

• D3 is the lookup value which is the top cell of the column Product List 2.
• $C$3:$C$12 is the table array which is the range of the column Product List 1.
• FALSE is responsible for finding the exact matches between Product List 1 and Product List 2.
• 2 is the adjustment_number. You need to change this number according to your need.
• The SUM function adds 2 with the output of the MATCH function. Thus, we get the correct row number.
• The CONCATENATE function merges the phrase “Matched with Row “ with the output of SUM(MATCH(D3,$C$3:$C$12,FALSE),2).
• Finally, the IFERROR function replaces any error left by the MATCH function with blanks (“”).

Combine IFERROR, CONCATENATE, SUM, & MATCH functions to Vlookup for duplicates and return matches. To do that, go through these steps below:

1. Create a new column to keep the row numbers.
2. Select the top cell of the new column.
3. Write the formula: =IFERROR(CONCATENATE(“Matched with Row “,SUM(MATCH(D3,$C$3:$C$12,FALSE),2)),””)
   
   
4. Hit ENTER.
   
5. Copy down the above formula to the entire column with Fill Handle icon.

In the column named “Duplicate Product”, you will see the row number of the data from the Product List 1 column that matches with the data from the column “Product List 2”.

• How to Compare Rows in Excel for Duplicates (7 Ways)
• How to Find Duplicates in Two Columns in Excel (7 Methods)

Vlookup for Duplicate and Return Matches in Excel Using MATCH, IF, & IFERROR Functions

In this case, I will write a formula using the MATCH, IF, & IFERROR functions. This formula will look for the duplicate values between Product List 1 and Product List 2. Then it will return the duplicate product names from Product List 2 into the column “Duplicate Products”.

Syntax

=IFERROR(IF(MATCH(lookup_value, lookup_array, [match_type])>0,D3,""),"")

Formula

=IFERROR(IF(MATCH(D3,$C$3:$C$12,FALSE)>0,D3,""),"")

Formula Breakdown

• D3 is the lookup value which is the top cell of the column Product List 2.
• $C$3:$C$12 is the table array which is the range of the column Product List 1.
• FALSE is responsible for finding the exact matches between Product List 1 and Product List 2.
• IF(MATCH(D3,$C$3:$C$12,FALSE)>0,D3,””) returns the value in cell D3 if MATCH(D3,$C$3:$C$12,FALSE)>0 becomes true. Otherwise, it returns blanks (“”).
• Finally, the IFERROR function replaces any error left by the MATCH function with blanks (“”).

To Vlookup for duplicates in Excel, follow these steps below:

1. Select the top cell of the column “Duplicate Products”.
2. Write down the following formula: =IFERROR(IF(MATCH(D3,$C$3:$C$12,FALSE)>0,D3,””),””)
   
3. Press the ENTER button to insert the formula in cell E3.
   
   
4. Double-click on the Fill Handle icon or simply drag it down.

Duplicate values from the Product List 2 column are returned to the column “Duplicate Products”.

Vlookup to Identify Duplicate & Unique Values with IF, ISNA Functions in Excel

In this case, I will write a formula to Vlookup between Product List 1 and 2. Then in the column, “Duplicate Products” I will report either Duplicate or Unique against the data of Product List 2.

Syntax

=IF(ISNA(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])),Value_If_True,Value_If_False)

Formula

=IF(ISNA(VLOOKUP(D3,$C$3:$C$12,1,FALSE)),"Unique","Duplicate")

Formula Breakdown

• D3 is the lookup value which is the top cell of the column Product List 2.
• $C$3:$C$12 is the table array which is the range of the column Product List 1.
• 1 is the column index number which refers to the first column in the range $C$3:$C$12.
• FALSE is responsible for finding the exact matches between Product List 1 and Product List 2.
• The ISNA function returns TRUE if VLOOKUP(D3,$C$3:$C$12,1,FALSE) returns any #N/A error. Otherwise, If returns FALSE.
• The IF function returns Unique if the ISNA function returns TRUE. Otherwise, it returns Duplicate.

Identify duplicates & unique values with IF, ISNA and VLOOKUP functions. Here’s how:

1. Copy the formula below in the top cell of the column, “Duplicate Products”.
   
   
2. Press ENTER.
   
   
3. Copy down the above formula to the entire column with the Fill Handle icon or simply drag it down.

In the “Duplicate Products” column, the formula returns Duplicate if any value of Product List 2 matches with Product List 1. Otherwise, It returns Unique.

• 5 Ways to Find Matching Values in Two Worksheets in Excel
•  How to Find Similar Text in Two Columns in Excel (5 Ways)

Vlookup for Duplicate Values in Excel Creating a Search Box with Different Functions

Now, I will show you to create a search box where you can insert product names.

Syntax

=IF(IF(ISNA(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]))),0,1)+IF(ISNA(VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup]))),0,1)=2,Value_If_True,Value_If_False)

Formula

=IF(IF(ISNA(VLOOKUP(D14,C3:C12,1,FALSE)),0,1)+IF(ISNA(VLOOKUP(D14,D3:D12,1,FALSE)),0,1)=2,"Duplicate","Unique")

Formula Breakdown

• D14 is the lookup value that refers to a product name to justify whether it’s unique or not.
• C3:C12 is the table array which is the range of the column Product List 1.
• D3:D12 is the table array which is the range of the column Product List 2.
• 1 is the column index number which refers to the first column for both ranges C3:C12 & D3:D12.
• FALSE is responsible for finding the exact matches between Product List 1 and Product List 2.
• The ISNA function returns TRUE if the VLOOKUP function returns any #N/A error. Otherwise, If returns FALSE.
• The IF function returns 0 if the ISNA function returns TRUE. Otherwise, it returns 1.
• IF(ISNA(VLOOKUP(D14,C3:C12,1,FALSE)),0,1) returns 0 if the value in cell D14 matches with any data in the range C3:C12.
• IF(ISNA(VLOOKUP(D14,D3:D12,1,FALSE)),0,1) returns 0 if the value in cell D14 matches with any data in the range D3:D12.
• If the value in cell D14 exists both in Product List 1 and Product List 2, then IF(ISNA(VLOOKUP(D14,C3:C12,1,FALSE)),0,1)+IF(ISNA(VLOOKUP(D14,D3:D12,1,FALSE)),0,1) will return 2. Otherwise, it will return either 0 or 1.
• If IF(ISNA(VLOOKUP(D14,C3:C12,1,FALSE)),0,1)+IF(ISNA(VLOOKUP(D14,D3:D12,1,FALSE)),0,1)=2 becomes true the first IF function will return Duplicate. Otherwise, it will return Unique.

Create a search box with IF, ISNA, VLOOKUP functions to vlookup for duplicates in Excel, follow these steps below:

1. Select a blank cell.
2. Type the formula: =IF(IF(ISNA(VLOOKUP(D14,C3:C12,1,FALSE)),0,1)+IF(ISNA(VLOOKUP(D14,D3:D12,1,FALSE)),0,1)=2,”Duplicate”,”Unique”)
3. Hit ENTER.

Vlookup  for Duplicate Values b/w 2 Worksheets with IF, ISERROR & VLOOKUP functions in Excel

In the worksheet named “Formula 6A”, there are two columns named “Category” and “Product List 1”.

In another worksheet named “Formula 6B”, there are two columns named “Category” and “Product List 2”.

Now I will show you how you can Vlookup between these two datasets from two different worksheets. I have created an additional column named “Product Status” adjacent to the column, “Product List 1” in the worksheet “Formula 6A”.

In this column, I will return “Duplicate” if any value from Product List 1 matches that of Product List 2. For this purpose, I will write a formula using the VLOOKUP, ISERROR, and IF functions.

Syntax

=IF(ISERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])),Value_If_True,Value_If_False)

Formula

=IF(ISERROR(VLOOKUP(C3,'Formula 6B'!$C$3:$C$12,1,0)),"Unique", "Duplicate")

Formula Breakdown

• C3 is the lookup value which is the top cell of the column Product List 1.
• ‘Formula 6B’ is the other worksheet name.
• $C$3:$C$12 is the table array which is the range of the column Product List 2.
• 1 is the column index number which refers to the first column in the range $C$3:$C$12.
• 0 is responsible for finding the exact matches between Product List 1 and Product List 2.
• The IF function return “Duplicate” if any value from Product List 1 matches that of Product List 2 from the worksheet “Formula 6B”. Otherwise, it returns “Unique”.

Apply formula with IF, ISERROR and VLOOKUP functions for vlookup duplicate values in Excel, here are the steps below:

1. Select the top cell of the column.
2. Copy the following formula: =IF(ISERROR(VLOOKUP(C3,’Formula 6B’!$C$3:$C$12,1,0)),”Unique”, “Duplicate”)
   
   
3. Press ENTER to insert the formula in cell D3.
   It will return “Unique” there.
4. Double-click on the Fill Handle icon to copy the above formula down the entire column, “Product Status”.

In the column, “Product Status”, the formula will return “Unique” if any value from Product List 1 matches that of Product List 2. Otherwise, it returns “Duplicate”.

• How to Remove Duplicates in Excel [13 + Different Methods]
• Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

Vlookup  for Duplicate Values b/w 2 Workbooks in Excel with IF, ISERROR & VLOOKUp Functions

The dataset in the picture below is from a workbook named “Book2.xlsm”.

And the second dataset in the following picture is from a workbook named “Book1.xlsx”.

Now I will show you to vlookup between these two datasets from two different workbooks. I have created an extra column named “Product Status” right next to Product List 1 in the workbook “Book2.xlsm”.

To perform the task, I have created a formula using the VLOOKUP, ISERROR, & IF functions.

Syntax

=IF(ISERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])),Value_If_True,Value_If_False)

Formula

=IF(ISERROR(VLOOKUP(C3,'[Book1.xlsx]Formula 7B'!$C$3:$C$12,1,0)),"Unique", "Duplicate")

Formula Breakdown

• C3 is the lookup value which is the top cell of the column Product List 1.
• [Book1.xlsx] is the other workbook name.
• ‘Formula 7B’ is the worksheet name of the workbook “Book1.xlsx”.
• $C$3:$C$12 is the table array which is the range of the column Product List 2.
• 1 is the column index number which refers to the first column in the range $C$3:$C$12.
• 0 is responsible for finding the exact matches between Product List 1 and Product List 2.
• The IF function return “Duplicate” if any value from the Product List 1 matches that of Product List 2 from the worksheet “Formula 7B” of the workbook “Book1.xlsx”. Otherwise, it returns “Unique”.

Use IF, ISERROR & VLOOKUP functions for vlookup duplicates b/w two workbooks in Excel, go through the steps below:

1. Select a cell D3.
2. Type the following formula: =IF(ISERROR(VLOOKUP(C3,'[Book1.xlsx]Formula 7B’!$C$3:$C$12,1,0)),”Unique”, “Duplicate”)
   
   
3. Hit ENTER.
   It will return “Unique” in cell D3.
4. Double-click on the Fill Handle icon to AutoFil the above formula to the entire column.

In the column, “Product Status”, the formula will return “Unique” if any value from Product List 1 matches that of Product List 2. Otherwise, it returns “Duplicate”.

Vlookup for Duplicate Values Create a User-Defined Function with a VBA Code

Now, I will create a user-defined function using a VBA script. The user-defined function will vlookup for duplicate values between Product List 1 and Product List 2.

What’s special about this user-defined function is that it can identify 1st, the 2nd, and 3rd occurrences of a duplicate value. Based on the identification, it can return those values in a separate column.

Write a VBA code to create user-defined function to vlookup for duplicates and return 1st, 2nd, 3rd, etc. occurrences in Excel. So, go through these steps below:

1. Open the Visual Basic Editor.
   You can open it using one of the 3 ways:
   • Right-click on the sheet name and choose View Code.
   • Press ALT + F11.
   • Click on Visual Basic in the Code group of the Developer tab from the main ribbon.

   
2. Open a new module. To do that, click on the Insert tab and choose Module.
   
   
3. Copy the VBA script below.
   

   Function NthVlookup(x1, x2 As Range, cx3 As Integer, x4)
   
   Dim xRow1 As Long
   Dim xVal1 As Integer
   Dim xFound1 As Boolean
   
   NthVlookup = " "
   
   With x2
   For xRow1 = 1 To .Rows.Count
   If .Cells(xRow1, 1).Value = x1 Then
   xVal1 = xVal1 + 1
   End If
   If xVal1 = x4 Then
   NthVlookup = .Cells(xRow1, cx3).Text
   Exit Function
   End If
   Next xRow1
   End With
   
   End Function

4. Press CTRL + V to paste the code into the Visual Basic Editor.
5. Save the workbook as Excel Macro-Enabled Workbook from the File tab.

Now, come back to the worksheet where you want to vlookup.

A. Return the Duplicates for the 1st Occurrence

Use the following formula to return duplicates from Product List 1 having only one copy exists in Product List 2.

Syntax

=NthVlookup(lookup_value, table_array, col_index_num,occurrence)

Formula

=NthVlookup(C3,$D$3:$D$12,1,1)

Formula Breakdown

• C3 is the lookup value which is the top cell of the column Product List 1.
• $D$3:$D$12 is the table array which is the range of the column Product List 2.
• 1 is the column index number which refers to the first column in the range $D$3:$D$12.
• 1 is the occurrence number that refers to the 1st occurrence.

To return duplicates for 1st occurrence in Excel, follow these steps:

1. Select the top cell of column “1st Occurrence”
2. Insert the following formula: =NthVlookup(C3,$D$3:$D$12,1,1)
   
   
3. Press ENTER.
   
   
4. Double-click on the Fill Handle icon to copy the formula down the entire column.
   

In the 1st Occurrence column, you will see only the duplicates from Product List 1 having only one copy exists in Product List 2.

B. Return the Duplicates for the 2nd Occurrence

Use the following formula to return duplicates from Product List 1 having at least two copies exist in Product List 2.

Syntax

=NthVlookup(lookup_value, table_array, col_index_num,occurrence)

Formula

=NthVlookup(C3,$D$3:$D$12,1,2)

Formula Breakdown

• C3 is the lookup value which is the top cell of the column Product List 1.
• $D$3:$D$12 is the table array which is the range of the column Product List 2.
• 1 is the column index number which refers to the first column in the range $D$3:$D$12.
• 2 is the occurrence number that refers to the 2nd occurrence.

Follow these steps to return duplicates for the 2nd occurrence:

1. Click on the top cell of column “2nd Occurrence”.
2. Copy and paste the following formula: =NthVlookup(C3,$D$3:$D$12,1,2)
   
   
3. Press ENTER.
4. Double-click on the Fill Handle icon to copy the formula down the entire column.

In the 2nd Occurrence column, you will see only the duplicates from Product List 1 having at least two copies exist in Product List 2.

C. Return the Duplicates for the 3rd Occurrence

Use the following formula to return duplicates from Product List 1 having at least 3 copies existing in Product List 2.

Syntax

=NthVlookup(lookup_value, table_array, col_index_num,occurrence)

Formula

=NthVlookup(C3,$D$3:$D$12,1,3)

Formula Breakdown

• C3 is the lookup value which is the top cell of the column Product List 1.
• $D$3:$D$12 is the table array which is the range of the column Product List 2.
• 1 is the column index number which refers to the first column in the range $D$3:$D$12.
• 3 is the occurrence number that refers to the 3rd occurrence.

Go through these steps below with Vlookup functon to return duplicates for 3rd occurrence:

1. Select the top cell of column “3rd Occurrence”.
2. Type this formula: =NthVlookup(C3,$D$3:$D$12,1,3)
   
   
3. Press ENTER.
4. Double-click on the Fill Handle icon to copy the formula down the entire column.

In the 3rd Occurrence column, you will see only the duplicates from Product List 1 having at least 3 copies existing in Product List 2.

•  5+ Formulas to Find Duplicates in One Column in Excel
• 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

Conclusion

To sum up, I have discussed 8 scenarios to vlookup for duplicate values and return the matches in Excel. Leave a comment in the comment section below if you find any issues regarding the above cases.

Frequently Asked Questions

Why is my VLOOKUP returning duplicates?

VLOOKUP may return duplicates if your lookup range contains repeated values. To address this, ensure your lookup range has unique entries. Alternatively, use INDEX and MATCH with IFERROR to avoid duplicates: =IFERROR(INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), “”)

How do I get VLOOKUP to ignore duplicates?

To make VLOOKUP ignore duplicates in Excel, use the IFERROR function in combination with INDEX and MATCH. This formula ensures that only the first matching value is returned while ignoring subsequent duplicates. Example: =IFERROR(INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), “”)

Does VLOOKUP pick the first match?

Yes. VLOOKUP in Excel retrieves the first match it encounters in the specified lookup range. If there are multiple matches, it only returns the value associated with the first occurrence. Ensure accurate data organization to obtain the desired result when using VLOOKUP.