This tutorial explains how to Vlookup and combine values in Google Sheets using array as well as non-array formulas. On the course, you will get to know some of the stunning Google Sheets tips, ideas, and tricks.

If there are multiple occurrences of a lookup value (search_key) in the lookup column (the first column in the Vlookup range), generally, the Vlookup function in Google Sheets will only return the value that corresponds to the first occurrence.

This may not be ideal for some situations. Luckily, Google Sheets has functions like Filter and Query to solve this puzzle.

Using Query or Filter with Join or Textjoin, we can easily solve the above mentioned Vlookup issue. But in this method, we can only use one search_key.

Vlookup and Combine Values in Google Sheets – Non-Array Alternative

Here is one example to the Vlookup and concatenate values.

If we use the following ArrayFormula Vlookup in cell E2, we will only get the value “p1” in the cells E2, E3, and E4. These are the values correspond to the first occurrences of the search key “a”, “b”, and “c”.

=ArrayFormula(vlookup(D2:D4,A2:B9,2,0))

But using the following Filter + TextJoin in cell E2, we get the proper result, i.e. p1, p2, p3, for the first search key which is “a”.

=textjoin(", ",true,filter($B$2:$B,$A$2:$A=D2))

For the search keys “b” and “c” we must use the above formula in cell E2, then drag-down until cell E4.

This Query will also work in this case.

=textjoin(", ",true,query($A$2:$B,"Select B where A='"&D2&"'",0))

Related: Merge Duplicate Rows in Google Sheets and Concatenate Values.

The above are the two non-array alternatives to Vlookup and combine values in Google Sheets.

I have one array formula also to do the same. But that formula would return a slightly different result.

For example, if the search key is “a” and the result is p1, p2, p3, p3, then the formula would only return the unique values. The last “p3” will be skipped.

Let’s write the array formula to Vlookup and combine values.

Vlookup and Combine Values in Google Sheets – Array Formula

There are several steps (6 steps) involved, but worth the try.

At first, we will use several helper columns. Finally, we can combine the formulas in the helper columns.

That means without using helper columns we can Vlookup and combine values in Google Sheets.

Though there are several steps involved, you can stop at the second step itself. Because, after the first two steps itself, you would get the result similar to the expected one.

The difference between the second step result with the final result is, in the second step, the values will be placed in multiple columns instead of combined using a delimiter such as a comma.

Here is my sample sheet. Copy it and then follow the steps below.

Sample_Sheet_131020

STEP 1

Cumulative Count of First Column in the Table

Let’s use a running count formula in cell C2 to generate the cumulative count of the the first column in the table.

=ARRAYFORMULA(COUNTIFS(A2:A9,A2:A9,ROW(A2:A9),"<="&ROW(A2:A9)))

I have used A2:A9 as the range. Once we complete all the steps, I will explain how to change A2:A9 to A2:A.

STEP 2

Group Search Key Column and Pivot Cumulative Count

This is the key step in Vlookup search keys and combine values in Google Sheets. Here we are going to use Query aggregation of text strings.

Use the following Query in cell D2.

=query({A2:C9},"Select Col1,max(Col2) group by Col1 pivot Col3",0)

Formula Explanation:

The above formula without the last part pivot Col3 will group the first column and max the second column.

Here is an example.

=query({A2:C9},"Select Col1,max(Col2) group by Col1",0)

The result would be;

	max
a	p3
b	p9
c	p5

You may please note that the max column is a text column. In Query, the MAX function will work in the text column also.

Since we have used the third (running count) column in the pivot clause, it acts as a subgroup and so the Query populates the values as per the table in the above image (D2:G5).

Now we just need to combine the columns E, F, and G to get the result that we expect from Vlookup and combine values.

If you don’t want to combine, you can stop at this point. But I suggest you to read on as you can find more cool tips below.

STEP 3

Query Offset to Remove Pivot Header

This is a quite simple step. Here I am just removing the Query pivot header row (E2:G2) that contains the numbers 1, 2, and 3.

I am using the below formula in cell H2 (skipping the screenshot as there are no other noticeable changes in comparison to the earlier result other than removing the header row).

=query({D2:G5},"Select * offset 1",0)

STEP 4

Vlookup Query Result

We are very close to Vlookup and combine values using an array formula in Google Sheets.

In E8:E10, I have entered the Vlookup search_keys.

The following Vlookup formula in cell F8 uses the range H2:K4, which is the step 3 result.

=ArrayFormula(vlookup(E8:E10,H2:K4,{2,3,4},0))

Note:

See the index column numbers within curly braces.

We know there are only 4 columns in the Vlookup range H2:K4 and we want values from columns 2, 3, and 4 in that range.

But sometimes there may be more columns in the range depending on our table in A2:B9.

So, later, somewhere in the final steps, we will use 10 index columns to accommodate future columns.

For that, we can use the index as {2,3,4,5,6,7,8,9,10,11} or in a dynamic way such as SEQUENCE(1,10,2). I would prefer the latter.

STEP 5

Formula to Vlookup and Combine Multiple Values in Google Sheets

We have used Vlookup in the earlier step. Now we want to combine the result. Here is my answer to Vlookup and combine values in Google Sheets.

We can use Query function to combine columns in the Vlookup result. Let’s use the below Query in the cell J8.

=ArrayFormula(transpose(query(transpose(F8:H10&","),,9^9)))

STEP 6

Remove Extra Separators from the Vlookup Combined Values

As you can see from the result, all the cells have extra delimiters at the end.

If we use the TEXTJOIN instead of Query, this won’t happen. But we can’t use the TEXTJOIN as it will combine the rows too.

Since we have no easier option other than Query for combining values in rows as above, we have to find a way to remove the unwanted delimiters from the end.

Here comes the importance of the text function called Regexreplace.

We can use Regexreplace to remove repeated characters from the end of strings. Here is that formula in cell J13.

=ArrayFormula(regexreplace(REGEXREPLACE(J8:J10,"(\s\,){1,}$",""),"(\,){1,}$",""))

How to Combine the Formulas Given in Steps 1 to 6?

We have written an array formula to Vlookup and combine multiple values in Google Sheets. But the formula uses several helper columns. So let’s combine the formulas.

You may please follow the below sequence to combine the formulas.

Replace STEP 2 forma reference {A2:C9} as below.

{A2:B9,C2:C9}

Then replace C2:C9 with the STEP 1 formula. Here is the formula after the said changes.

=query({A2:B9,ARRAYFORMULA(COUNTIFS(A2:A9,A2:A9,ROW(A2:A9),"<="&ROW(A2:A9)))},"Select Col1,max(Col2) group by Col1 pivot Col3",0)

Now you can remove the formula in cell C2.

Replace the reference {D2:G5} in the STEP 3 formula with the above formula.

Here is the formula after the said modification.

=query(query({A2:B9,ARRAYFORMULA(COUNTIFS(A2:A9,A2:A9,ROW(A2:A9),"<="&ROW(A2:A9)))},"Select Col1,max(Col2) group by Col1 pivot Col3",0),"Select * offset 1",0)

Now you can delete the formula in cell D2.

Replace H2:K4 in the STEP 4 formula with the above formula.

(the formula after the modification as above)

=ArrayFormula(vlookup(E8:E10,query(query({A2:B9,ARRAYFORMULA(COUNTIFS(A2:A9,A2:A9,ROW(A2:A9),"<="&ROW(A2:A9)))},"Select Col1,max(Col2) group by Col1 pivot Col3",0),"Select * offset 1",0),sequence(1,3,2),0))

Delete the formula in cell H2.

Replace F8:H10 in the STEP 5 formula with the above formula.

(the formula after the modification as above)

=ArrayFormula(transpose(query(transpose(ArrayFormula(vlookup(E8:E10,query(query({A2:B9,ARRAYFORMULA(COUNTIFS(A2:A9,A2:A9,ROW(A2:A9),"<="&ROW(A2:A9)))},"Select Col1,max(Col2) group by Col1 pivot Col3",0),"Select * offset 1",0),sequence(1,3,2),0))&","),,9^9)))

Delete F8 formula.

Replace J8:J10 in the STEP 6 formula with the above formula and then remove J8 formula.

(the formula after the modification as above)

=ArrayFormula(regexreplace(REGEXREPLACE(ArrayFormula(transpose(query(transpose(ArrayFormula(vlookup(E8:E10,query(query({A2:B9,ARRAYFORMULA(COUNTIFS(A2:A9,A2:A9,ROW(A2:A9),"<="&ROW(A2:A9)))},"Select Col1,max(Col2) group by Col1 pivot Col3",0),"Select * offset 1",0),sequence(1,3,2),0))&","),,9^9))),"(\s\,){1,}$",""),"(\,){1,}$",""))

Just cut (Ctrl+x) the Vlookup criteria from the range E8:E10 and paste (Ctrl+v) into M2:M4.

Insert the above formula in cell N2.

Delete all the columns from C to K and voila!

Vlookup and Combine Values – Open (Infinite) Criteria and Data Range

For using the formula in an open/infinite range, there are three changes that you must make in the above Vlookup and combine values formula in Google Sheets.

What are they?

Change # 1:

The formula may only return 3 comma-separated values in each row due to the index in Vlookup which is sequence(1,3,2). Change this to sequence(1,10,2) to get up to 10 values in each row.

While doing so, replace the ARRAYFORMULA just before the Vlookup with IFERROR. So that we can avoid error values such as #REF! (out of the range index column) and #N/A! (search_key not available) in the result.

Change # 2:

There are three ArrayFormula functions within the formula. You can remove the two inner ones. Do it carefully!

That means, when you remove one ArrayFormula find and remove the corresponding closing bracket.

Change # 3:

To make the formula work for open (infinite) ranges change max(Col2) group by Col1 with max(Col2) where Col1 is not null group by Col1. Then make all the ranges open. I mean to replace A2:A9 with A2:A, A2:B9 with A2:B etc.

That’s all about how to Vlookup and combine values in Google Sheets.