# How to Apply Vlookup Formula on Duplicate Values in Excel

We may not succeed in getting what we want while applying a vlookup formula on duplicate values. This is because of the way the **VLOOKUP **function handles the duplicate values. That’s why it is important to set how your vlookup formula will work on the duplicate values. Well if you are not satisfied with the performance of your vlookup formula follow along with this article. Because here I’m going to share some tips to apply the vlookup formula correctly on the duplicate values in Excel.

## How VLOOKUP Function Handles Duplicate Values

In the **Employee Name **column, some employee names have been repeated a couple of times. But that doesn’t mean those employee names are duplicates.

If you look closely, you will see that each employee’s name has a separate **Employee ID **as well as their **Country **of origin. Now, I want to extract the country name in column **F**, for each of the employee names in column **E**.

**Vlookup Formula**

=VLOOKUP(E2,$B$2:$C$10,2,FALSE)

After applying the vlookup formula in the **F **column, we can see that the **VLOOKUP function** returns only the first instance for each of the duplicate values.

The first employee named **Julia Flores’ **country is the **Netherlands**. The second employee named **Julia Flores’s **country is **France**. But the vlookup formula is returning to the **Netherlands**.

Thus, it’s clear that the **VLOOKUP **function only returns the first instance of the duplicates. This is really frustrating if you look for all the instances of the duplicates to return.

## Apply Vlookup on Duplicates and Return Every Instance of the Duplicates

Look at the following picture. There we’ve got all the country names against all the employee names correctly. To get the following resort, we have to go through another additional step before applying the vlookup formula.

### First Step: Make the Duplicates Unique First

We have to convert all the duplicate values into unique values. We will do that using the **COUNTIF function**.

I’ve taken an additional column before the main data table starts. There I’ve used the following formula to make all the duplicate values unique.

**Formula Syntax**

=COUNTIF (range, criteria)

**Formula**

=COUNTIF($C$2:C2,C2)&"-"&C2

**Formula Explanation**

As we copy down the formula the formula changes as **COUNTIF($C$2:C2,C2)&”-“&C2, COUNTIF($C$2:C3,C3)&”-“&C3, COUNTIF($C$2:C4,C4)&”-“&C4**. If you look closely, you will see that the range inside the **COUNTIF **function expands such as **$C$2:C2, $C$2:C3, $C$2:C4,** etc.

So, within the range **$C$2:C2, C2** represents **Julia Flores **who appears once. Thus, the **COUNTIF **function returns **1**. Then the range changes to **$C$2:C3**. In this range, **C2 **changes to **C3 **which represents **Robi **Nath. So far it’s the first appearance of **Robi Nath **in the range of **$C$2:C3**. Thus, the **COUNTIF **function returns** 1** again.

After that, the range becomes **$C$2:C4**. Within this range, **Julia Flores **appears for the second time. Thus, the function returns **2**. And it continues like this.

So, **COUNTIF($C$2:C2,C2)** returns the number of appearances for each of the employee names. Then, each **Employee Name **is merged with their corresponding number of appearances with a dash sign in-between. This is how the formula converts each duplicate value to unique values.

### Second Step: Apply Vlookup Formula on Duplicate Values Now

Now, it’s time to apply the vlookup formula to extract each instance of the duplicate values. Use the following vlookup formula to extract country names in the **G **column for each of the employee names in the **F** column. This formula is made up of the **VLOOKUP **& **COUNTIF **functions.

**Formula Syntax**

=VLOOKUP (COUNTIF (range, criteria), table_array, col_index_num, [range_lookup])

**Formula**

=VLOOKUP(COUNTIF($F$2:F2,F2)&"-"&F2,$A$2:$D$10,4,FALSE)

**Formula Explanation**

**COUNTIF($F$2:F2,F2)&”-“&F2:**This part of the formula adds the order of occurrences of each of the employee names with their corresponding employee names.**Read**how this part works. Then it serves as the lookup value for the**VLOOKUP****$A$2:$D$10:**It’s the table array. In this range, the first column (i.e. column**A**) serves as the lookup array. And the last column (i.e. column**D**) serves as the return array.**4**is the column index number. Here,**4**refers to the**4th**column of the table array which is column**D**.- Finally,
**False**represents an exact match between the lookup value and the lookup array.

After applying the formula and copying it down to the entire **Country **column, you will get all the instances of each duplicate value correctly.

## Conclusion

So, I’ve discussed how to apply vlookup on the duplicate values in Excel. I reckon you’ve found this article useful. You can read more articles relating to Excel from the **Blog** page of our website. Have a fantastic workday!

## Frequently Asked Questions

### Why is my VLOOKUP returning duplicates?

**VLOOKUP** may return duplicates due to multiple matching values in the lookup range. To address this, consider using the **UNIQUE** function in conjunction with **VLOOKUP**. For example: **=VLOOKUP(A2, UNIQUE($B$2:$C$10), 2, FALSE)**

This formula looks up the value in **A2** within the unique values of the range **B2:C10**. The **UNIQUE** function filters out duplicates, ensuring a single result for **VLOOKUP**. Verify data integrity and ranges for accurate results.

### How do I ignore duplicates in Excel?

Exclude duplicates in** VLOOKUP** by utilizing the **IF** and **COUNTIF** functions. For example, if your lookup range is in column A and you want to find a value in cell **B1**, use the formula: **=IF(COUNTIF($A$1:$A$100, B1)=1, VLOOKUP(B1, $A$1:$D$100, 2, FALSE), “Duplicate”)**

This formula checks if the value in B1 appears only once in column** A**. If so, it performs the **VLOOKUP**; otherwise, it returns **‘Duplicate’**.

### Does VLOOKUP require unique values?

No, **VLOOKUP** does not strictly require unique values. However, when dealing with duplicates in the lookup range, it may return the first match found. To ensure accurate results, consider using the **UNIQUE** function to filter out duplicates in the lookup range. Example: **=VLOOKUP(A2, UNIQUE($B$2:$C$10), 2, FALSE)**

This enhances accuracy by working with unique values and mitigating potential duplicates in the **VLOOKUP** process.

This was really helpful, thank you so much. I’ll be visiting your site more often

Thank you!