This tutorial can help you to properly use Vlookup on duplicates in Google Sheets.

Assume, the search column (first column in your data range) contain duplicates. Then using Vlookup in the default way would only return the value of the first match.

Take a look at this example.

If use Vlookup as below (the standard form) it would only return “Black” (the value from the second column of the first occurrence of the search key).

=vlookup(D2,A2:B7,2,FALSE)

The same search key “Car” repeats twice in the first column. So if you want to return the value from the second column from these two rows (as shown on the image above), you must look for alternatives.

Using Vlookup Function on Duplicate Values in Google Sheets

If the Vlookup search column contains duplicates, the function can search the first column for the first occurrence of the search key. But there are workarounds to search the;

1. 1st duplicate value in the first column, the 2nd duplicate value in the first column, and so on to return the corresponding value in the row found. This, I have already detailed – Vlookup to Find Nth Occurrence in Google Sheets.
2. All the duplicate values in the first column and return values from the the rows found.

In this post, I am going to detail the second point that about performing Vlookup function on duplicates.

There are two options. The simplest way is using the function Filter. Also, you can use Vlookup itself as a combination formula and this formula is more powerful. I am going to explain all these below.

Filter Function as an Alternative to Vlookup When Duplicate Values in Search Column

Hope you have already set up the sample data above (first screenshot). Now use the below Filter formula.

=filter($B$2:$B,$A$2:$A=D2)

This formula would filter column B if column A value is “Car”. The output will be in two rows. So make them in a column using Transpose as below.

=transpose(filter($B$2:$B,$A$2:$A=D2))

The said Filter formula is in cell E2. To repeat the lookup for another search key, just drag the fill handle as follows.

Many Google Sheets users, including me, like array formulas wherever it’s possible. We can avoid copying the formula as above using a combination formula.

You can set up a self-expanding Vlookup formula for this case. Vlookup on duplicates is possible in Google Sheets but in a combo form.

Vlookup on Duplicates Using a Combo Array Formula in Google Sheets

The Vlookup function can expand its results. We can make use of that feature here. As an example see the below Vlookup formula.

Earlier we have used the sample data in A2:B. See the same data in E2:G. In this, I have removed the duplicates in the first column and moved its second column values to the corresponding rows.

Assume cell H2 contain the search key “Car”.

=ArrayFormula(Vlookup(H2,E2:G4,{2,3},FALSE))

This formula would return the colors “Black” and “Candy White” (see the detailed tutorial on this use here – Multiple Values Using Vlookup in Google Sheets is Possible).

This opens a new possibility. If we can generate the same data in the range E2:G from the data in A2:B using a formula, that we can use in Vlookup as a range.

I have the formula for this;

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

and its explanation here – How to Aggregate Strings Using Query in Google Sheets.

In the below example, I am going to use this formula as the Vlookup range.

=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 D2 is the search key, the Query formula is the range and {2,3} is the number of columns to return.

Make it array by modifying it as below.

Use D2:D as for the search key range, {2,3,4,5} as index column instead of {2,3}. Because there may be more colors for the same vehicle later in the range. In our virtual range, different colors against the same vehicle occupy different columns.

Finally, wrap the formula with an Iferror.

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

The formula is ready to expand.

That’s all. Hope you could learn how to use Vlookup on duplicates in Google Sheets. Enjoy!

Additional Vlookup Resources:

1. How to Skip Blank Cells in Vlookup in Google Sheets.
2. Dynamic Index Column in Vlookup in Google Sheets
3. Vlookup to Only Return Values from Max Rows in Google Sheets.
4. How to Perform Two-way Lookup Using Vlookup in Google Sheets.
5. How to Vlookup a Date Range in Google Sheets.
6. Vlookup Skips Hidden Rows in Google Sheets.
7. Partial Match in Vlookup in Google Sheets.
8. Vlookup Result Plus Next ‘n’ Rows in Google Sheets.
9. Vlookup from Bottom to Top in Google Docs Sheets.