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

Table of Contents

## How VLOOKUP Function Handles Duplicates 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.

**Syntax of the Formula**

=COUNTIF (range, criteria)

**Formula**

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

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.

Now 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])

**The Formula**

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

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

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

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