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

- Create a separate column to extract the duplicate values.

Here, I’ve created a column named**“Duplicate Products”**. - Copy the following formula of
**IFERROR**and**VLOOKUP**functions in the**top cell**of the new column. - Press
**ENTER**to insert the formula.

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

- Create a new column to keep the row numbers.
- Select the
**top cell**of the new column. - Write the formula:
**=IFERROR(CONCATENATE(“Matched with Row “,SUM(MATCH(D3,$C$3:$C$12,FALSE),2)),””)**

- Hit
**ENTER**.

- 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:

- Select the top cell of the column
**“Duplicate Products”**. - Write down the following formula:
**=IFERROR(IF(MATCH(D3,$C$3:$C$12,FALSE)>0,D3,””),””)**

- Press the
**ENTER**button to insert the formula in cell**E3**.

- 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:

- Copy the formula below in the top cell of the column,
**“Duplicate Products”.**

- Press
**ENTER**.

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

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

- Select a blank cell.
- 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”)** - 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:

- Select the top cell of the column.
- Copy the following formula:
**=IF(ISERROR(VLOOKUP(C3,’Formula 6B’!$C$3:$C$12,1,0)),”Unique”, “Duplicate”)**

- Press
**ENTER**to insert the formula in cell**D3**.

It will return**“Unique”**there. - 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:

- Select a cell
**D3**. - Type the following formula:
**=IF(ISERROR(VLOOKUP(C3,'[Book1.xlsx]Formula 7B’!$C$3:$C$12,1,0)),”Unique”, “Duplicate”)**

- Hit
**ENTER**.

It will return**“Unique”**in cell**D3**. - 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:

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

- Right-click on the sheet name and choose
- Open a new module. To do that, click on the
**Insert**tab and choose**Module**.

- 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`

- Press
**CTRL + V**to paste the code into the**Visual Basic Editor.** - 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:

- Select the top cell of column
**“1st Occurrence”** - Insert the following formula:
**=NthVlookup(C3,$D$3:$D$12,1,1)**

- Press
**ENTER**.

- 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:

- Click on the top cell of column
**“2nd Occurrence”.** - Copy and paste the following formula:
**=NthVlookup(C3,$D$3:$D$12,1,2)**

- Press
**ENTER**. - 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:

- Select the top cell of column
**“3rd Occurrence”**. - Type this formula:
**=NthVlookup(C3,$D$3:$D$12,1,3)**

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