![]() ![]() This is how the formula converts each duplicate value to unique values. Then each Employee Name is merged with their corresponding number of appearances with a dash sign in-between. So, COUNTIF($C$2:C2,C2) returns the number of appearances for each of the employee names. Within this range, Julia Flores appears for the second time. Thus, the COUNTIF function returns 1 again.Īfter that, the range becomes $C$2:C4. So far it’s the first appearance of Robi Nath in the range of $C$2:C3. In this range, C2 changes to C3 which represents Robi Nath. So within the range $C$2:C2, C2 represents Julia Flores who appears once. 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. There I’ve used the following formula to make all the duplicate values unique.įormula Syntax =COUNTIF (range, criteria)Īs 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. I’ve taken an additional column before the main data table starts. We will do that using the COUNTIF function. We have to convert all the duplicate values into unique values. To get the following resort, we have to go through another additional step before applying the vlookup formula.įirst Step: Make the Duplicates Unique First There we’ve got all the country names against all the employee names correctly. Apply Vlookup on Duplicates and Return Every Instance of the Duplicates This is really frustrating if you look for all the instances of the duplicates to return. Thus, it’s clear that the VLOOKUP function only returns the first instance of the duplicates. But the vlookup formula is returning to the Netherlands. The second employee named Julia Flores’s country is France. The first employee named Julia Flores’ country is the Netherlands. Vlookup Formula =VLOOKUP(E2,$B$2:$C$10,2,FALSE)Īfter 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. Now I want to extract the country name in column F, for each of the employee names in column E. If you look closely, you will see that each employee’s name has a separate Employee ID as well as their Country of origin. But that doesn’t mean those employee names are duplicates. In the Employee Name column, some employee names have been repeated a couple of times. How VLOOKUP Function Handles Duplicates Values Second Step: Apply Vlookup Formula on Duplicate Values Now.First Step: Make the Duplicates Unique First.Apply Vlookup on Duplicates and Return Every Instance of the Duplicates.How VLOOKUP Function Handles Duplicates Values. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |