# Vlookup for Duplicate Values and Return the 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 the duplicate values** and return the matches in Excel.

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

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

**Here is the syntax of the formula:**

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

**Now Follow the 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)," ")

**In the formula above,**

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

** **

**🔗 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.

**Here is the syntax of the formula:**

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

**Now Follow the 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)),"")

**In the formula above,**

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

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

**Here is the syntax of the formula:**

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

**Now Follow the 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,""),"")

**In the formula above,**

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

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

**🔗 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.**

**Here is the syntax of the formula:**

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

**Now Follow the 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")

**In the formula above,**

**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 the **Product List 2** matches with **Product** **List 1**. Otherwise, It returns **Unique**.

**🔗 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.

**Here is the syntax of the formula:**

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

**Now Follow the 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**.

**In the formula above,**

**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.**- I
**F(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.

**Here is the syntax of the formula:**

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

**Now Follow the 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")

**In the formula above,**

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

**🔗 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.

**Here is the syntax of the formula:**

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

**Now Follow the 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")

**In the formula above,**

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

**🔗 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 the 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.

**Now Follow the 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.

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

**Here is the syntax of the formula:**

=NthVlookup(lookup_value, table_array, col_index_num,occurrence)

**Now Follow the Guide 🡻 **

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

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

**In the formula above,**

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

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

**Here is the syntax of the formula:**

=NthVlookup(lookup_value, table_array, col_index_num,occurrence)

**Now Follow the Guide 🡻 **

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

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

**In the formula above,**

**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 exist in **Product List 2.**

**Here is the syntax of the formula:**

=NthVlookup(lookup_value, table_array, col_index_num,occurrence)

**Now Follow the Guide 🡻 **

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

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

**In the formula above,**

**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 exist in **Product List 2.**

**🔗 5+ Formulas to Find Duplicates in One Column in Excel**

## Conclusion

To sum up, I have discussed 8 scenarios to vlookup for the 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.