How to Master Vlookup on Duplicates in Google Sheets

GiGiKing·March 18, 2024·0 comments

Have you ever found yourself in a situation in Google Sheets where you needed to perform a Vlookup on duplicate values? If you have, then this article is just what you need! I will guide you through the process of mastering Vlookup on duplicates in Google Sheets, ensuring that you can get accurate and reliable results every single time.

Understanding the Challenge

By default, when you use the Vlookup function in Google Sheets, it only returns the value of the first match it finds in the search column. This can be problematic if your search column contains duplicate values. Let’s take a look at an example to illustrate this:

Examples to Vlookup on duplicates in Google Sheets

In the above example, if you use the standard Vlookup formula, it would only return “Black” – the value from the second column of the first occurrence of the search key. However, if you want to retrieve the values from the second column for all the rows with the same search key, you need to explore alternative solutions.

Using Vlookup Function on Duplicate Values in Google Sheets

When the Vlookup search column contains duplicates, there are two ways to overcome this challenge:

Find the nth duplicate value in the first column and return the corresponding value in the row found. This method is detailed in the article “Vlookup to Find Nth Occurrence in Google Sheets,” which I highly recommend.
Retrieve all the duplicate values in the first column and return the corresponding values from the rows found.

In this article, we will focus on exploring the second method: performing Vlookup on duplicates. There are two approaches you can take – using the FILTER function or using a combination formula with Vlookup.

Leveraging the FILTER Function

The FILTER function is the simplest way to perform a Vlookup on duplicates. Let’s see how it works:

Set up your data range, as shown in the screenshot above.
Use the following formula to filter column B based on the value in column A:
=FILTER($B$2:$B,$A$2:$A=D2)
This formula will filter column B if the value in column A is “Car.” The output will be in two rows. To make them appear in a column, use the TRANSPOSE function:
=TRANSPOSE(FILTER($B$2:$B,$A$2:$A=D2))

By utilizing the FILTER function, you can easily retrieve all the values associated with duplicate keys in your search column.

Unleashing the Power of Combo Array Formula

For those who prefer using array formulas, we have an even more powerful solution. We will leverage a combination formula using the ARRAYFORMULA and Vlookup functions. Here’s how it works:

Set up your data range as before (see the screenshot above).
Remove the duplicates in the first column and move their corresponding values to the respective rows.
Assume cell H2 contains the search key “Car.”
Use the following formula to perform Vlookup on duplicates:
=ARRAYFORMULA(Vlookup(H2,E2:G4,{2,3},FALSE))

This formula will return the colors “Black” and “Candy White,” as demonstrated in the tutorial “Multiple Values Using Vlookup in Google Sheets is Possible.”

By expanding the results of the Vlookup function, we can achieve our goal. To generate the same data in the range E2:G from the data in A2:B, you can use the following formula:
=ARRAYFORMULA(query(query({A2:B,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<= "&ROW(A2:A))},"Select Col1, max(Col2) where Col1 is not null group by Col1 Pivot Col3"),"Select * offset 1",0))

For a detailed explanation of this formula, refer to the article “How to Aggregate Strings Using Query in Google Sheets.”

To combine all these elements and unleash the full potential of Vlookup, use the following expanded formula:
=ArrayFormula(Vlookup(D2,query(query({A2:B,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<= "&ROW(A2:A))},"Select Col1, max(Col2) where Col1 is not null group by Col1 Pivot Col3"),"Select * offset 1",0),{2,3},FALSE)

In this formula, D2 represents the search key, the Query formula serves as the range, and {2,3} indicates the number of columns to return.

Remember to array-formulate the formula by modifying it as follows:
=ArrayFormula(IFERROR(Vlookup(D2:D,query(query({A2:B,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<= "&ROW(A2:A))},"Select Col1, max(Col2) where Col1 is not null group by Col1 Pivot Col3"),"Select * offset 1",0),{2,3,4,5},FALSE)))

Now, the formula is ready to expand, providing accurate results for your Vlookup on duplicates needs.

Conclusion

Congratulations! You have now mastered the art of using Vlookup on duplicates in Google Sheets. By following the methods outlined in this article, you can retrieve the desired values from duplicate keys in your search column. Enjoy the power and flexibility that Vlookup offers in your Google Sheets projects.

For more resources on Vlookup and related topics, feel free to explore the articles below: