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

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.

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

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!