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

Rhidi Barma Excel Formulas

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.

Download Book1.xlsx
Download Book2.xlsm

Introduction to the Dataset

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.

Introduction to the Dataset

Case #1: Vlookup for Duplicate Values to Pull Data Based on Matches Using VLOOKUP & IFERROR Functions in Excel

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])," ")

Usage Guide

 Step_1: Create a separate column to extract the duplicate values.

Here, I’ve created a column named “Duplicate Products”.

Step_2: Copy the following formula of IFERROR and VLOOKUP functions in the top cell of the new column.

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

Vlookup for Duplicate Values to Pull Data Based on Matches Using VLOOKUP & IFERROR Functions in Excel

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 (“”).

Step_3: Press ENTER to insert the formula.

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

Step_5: To copy the formula down the entire column double-click on the icon.

Final Result

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

Output: Vlookup for Duplicate Values to Pull Data Based on Matches in Excel

🔗 How to Find Duplicates in Two Columns in Excel (7 Methods)

Case #2: Vlookup for Duplicate Values in Excel and Return Row Numbers of the Matched Data

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)),"")

Usage Guide

Step_1: Create a new column to keep the row numbers.

Step_2:  Write the formula below at the top cell of the new column.

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

Vlookup for Duplicate Values in Excel and Return Row Numbers of the Matched Data

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 (“”).

Step_3: Hit ENTER.

Step_4: Copy down the above formula to the entire column. For this, you can double-click on the Fill Handle icon or simply drag it down.

Final Result

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

Final Result: Vlookup for Duplicate Values in Excel and Return Row Numbers of the Matched Data

🔗 How to Compare Rows in Excel for Duplicates (7 Ways)

Case #3: Vlookup for Duplicate Values to Return Data Based on Matches Using MATCH, IF, & IFERROR Functions in Excel

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,""),"")

Usage Guide

Step_1: Write down the following formula in the top cell of the column, “Duplicate Products”.

=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 (“”).

Step_2: Press the ENTER button to insert the formula in cell E3.

Vlookup for Duplicate Values to Return Data Based on Matches Using MATCH, IF, & IFERROR Functions in Excel

Step_3: Copy down the above formula to the entire column. For this, you can double-click on the Fill Handle icon or simply drag it down.

Final Result

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

Output: Vlookup for Duplicate Values to Return Data Based on Matches in Excel

🔗 How to Find Similar Text in Two Columns in Excel (5 Ways)

Case #4: Vlookup in Two Columns and Identify Duplicate & Unique Values 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)

Usage Guide

Step_1: Copy the formula below in the top cell of the column, “Duplicate Products”.

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

Vlookup in Two Columns and Identify Duplicate & Unique Values in Excel

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.

Step_2: Press ENTER.

Step_3: Copy down the above formula to the entire column. For this, you can double-click on the Fill Handle icon or simply drag it down.

Final Result

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.

End Result: Vlookup in Two Columns and Identify Duplicate & Unique Values in Excel

🔗 5 Ways to Find Matching Values in Two Worksheets in Excel

Case #5: Create a Search Box Using VLOOKUP, IF, & IFNA Functions to Vlookup for Duplicate Values in Excel

Now I will show you to create a search box where you can insert product names. Then a formula will determine whether that product name is a duplicate or not.

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)

Usage Guide

Step_1: Type the formula below in cell D15.

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

Step_2: Hit ENTER.

Create a Search Box Using VLOOKUP, IF, & IFNA Functions to Vlookup for Duplicate Values in Excel

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.

🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

Case #6: Vlookup  for Duplicate Values Between Two Worksheets and Identify Duplicate & Unique Data 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)

Usage Guide

Step_1: Copy the following formula in the top cell of the column, “Product Status”.

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

Vlookup  for Duplicate Values Between Two Worksheets and Identify Duplicate & Unique Data in Excel

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

Step_2: Press ENTER to insert the formula in cell D3.

It will return “Unique” there.

Step_3: Double-click on the Fill Handle icon to copy the above formula down the entire column, “Product Status”.

Final Result

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

Final Result: Vlookup  for Duplicate Values Between Two Worksheets and Identify Duplicate & Unique Data in Excel

🔗 How to Remove Duplicates in Excel [13 + Different Methods]

Case #7: Vlookup  for Duplicate Values Between Two Workbooks and Identify Duplicate & Unique Data in Excel

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)

Usage Guide

Step_1: Type the following formula in cell D3 of the Product Status column.

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

Vlookup  for Duplicate Values Between Two Workbooks and Identify Duplicate & Unique Data in Excel

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

Step_2: Hit ENTER.

It will return “Unique” in cell D3.

Step_3: Double-click on the Fill Handle icon to AutoFil the above formula to the entire column.

Final Result

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

Output: Vlookup  for Duplicate Values Between Two Workbooks and Identify Duplicate & Unique Data in Excel

🔗 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel

Case #8: Create a User-Defined Function Using VBA Script to Vlookup for Duplicate Values and Return 1st, 2nd, 3rd, etc. Occurrences in Excel

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.

Usage Guide

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

Step_2: Open a new module. To do that, click on the Insert tab and choose Module.

Step_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

Step_4: Press CTRL + V to paste the code into the Visual Basic Editor.

Step_5: Save the workbook as Excel Macro-Enabled Workbook from the File tab.

Create a User-Defined Function Using VBA Script to Vlookup for Duplicate Values

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)

Usage Guide

Step_1: Insert the following formula in the top cell of column “1st Occurrence”.

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

Step_2: Press ENTER.

Vlookup for Duplicate Values and Return the 1st Occurrences in Excel

Step_3: Double-click on the Fill Handle icon to copy the formula down the entire column.

Final Result

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)

Usage Guide

Step_1: Insert the following formula in the top cell of column “2nd Occurrence”.

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

Vlookup for Duplicate Values and Return the 2nd Occurrences in Excel

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.

Step_2: Press ENTER.

Step_3: Double-click on the Fill Handle icon to copy the formula down the entire column.

Final Result

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)

Usage Guide

Step_1: Insert the following formula in the top cell of column “3rd Occurrence”.

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

Step_2: Press ENTER.

Step_3: Double-click on the Fill Handle icon to copy the formula down the entire column.

Final Result

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.

Vlookup for Duplicate Values and Return the 3rd Occurrences in Excel

🔗 5+ Formulas to Find Duplicates in One Column 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.

Data Analyst & Research Associate

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *