VLOOKUP and Combining Values in Google Sheets

Published on

This tutorial explains how to use VLOOKUP and combine values in Google Sheets using both array and non-array formulas. Along the way, you’ll uncover some of the most useful tips, ideas, and tricks for working with Google Sheets.

If there are multiple occurrences of a lookup value (search_key) in the lookup column (the first column in the VLOOKUP range), the VLOOKUP function in Google Sheets generally returns only the value corresponding to the first occurrence.

This behavior might not be ideal for all situations. Fortunately, Google Sheets offers functions like FILTER and QUERY to solve this challenge.

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

Here’s an example of how to use VLOOKUP and concatenate values:

VLOOKUP and combine values example in Google Sheets

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

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

However, using the following FILTER + UNIQUE + TEXTJOIN formula in cell E2, we get the correct result, i.e., “p1, p2, p3”, for the first search key, which is “a”:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER($B$2:$B, $A$2:$A = D2)))

For the search keys “b” and “c”, you would need to use the above formula in cell E2, then drag it down until cell E4.

VLOOKUP and Combine Values - Non-Array Formula Alternative Example

Explanation of the Formula:

  • The FILTER function filters the range B2:B wherever A2:A matches the key in D2.
  • The UNIQUE function returns unique values (optional).
  • The TEXTJOIN function combines those values, leaving a comma as the delimiter.

This is the non-array alternative for VLOOKUP and combining values in Google Sheets.

VLOOKUP and Combine Values in Google Sheets – Array Formula Alternatives

There are two different approaches for using an array formula.

You can transform the above FILTER-based formula into an unnamed lambda function and apply it to each element in the search key range (D2:D4) using a lambda helper function.

Alternatively, you can use a QUERY workaround as an alternative to VLOOKUP and combine values.

Approach 1: Using the LAMBDA Function

We can create a custom function based on the FILTER formula above as follows:

=LAMBDA(search_key, TEXTJOIN(", ", TRUE, UNIQUE(FILTER($B$2:$B, $A$2:$A = search_key))))

You can test it by specifying the function call with D2, like this:

=LAMBDA(search_key, TEXTJOIN(", ", TRUE, UNIQUE(FILTER($B$2:$B, $A$2:$A = search_key))))(D2)

This is equivalent to the FILTER formula above. Now, we can convert it into an array formula by specifying the lambda function within the MAP function, which iterates over each element in the array D2:D4 and returns an array result.

The syntax for the MAP function is:

=MAP(array1, [array2, …], lambda)

Where array1 is D2:D4, the search key range, and lambda is the above lambda function without the function call.

=MAP(D2:D4, LAMBDA(search_key, TEXTJOIN(", ", TRUE, UNIQUE(FILTER($B$2:$B, $A$2:$A = search_key)))))

In our example, you can use this formula in cell E2 after clearing the range E2:E4.

Approach 2: Using a QUERY Workaround

In this approach, I will explain the process step by step so you can understand how the formula develops and learn new techniques.

Step 1:

=ArrayFormula(IF(A2:A = "",, HSTACK(A2:A & "_", B2:B & ",")))

This array formula adds an underscore at the end of the values in A2:A and a comma at the end of the values in B2:B if A2:A is not empty.

Step 1 - Adding an underscore to the first column and a comma to the second column

Step 2:

=ArrayFormula(QUERY(IF(A2:A = "",, HSTACK(A2:A & "_", B2:B & ",")), "SELECT MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col2 PIVOT Col1"))
  • SELECT MAX(Col2) WHERE Col1 IS NOT NULL: Returns the maximum value in column 2 (i.e., p9,).
  • SELECT MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col2: Returns the unique values in column 2 (i.e., { p1,, p2,, p3,, p4,, p5,, p9,}).
  • SELECT MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col2 PIVOT Col1: Pivots the unique values in Col1 (e.g., a_, b_, c_) into column headers and places all the corresponding unique values from column 2 under the relevant columns.
Grouping and pivoting strings for combining values

Step 3:

=ArrayFormula(TRANSPOSE(QUERY(QUERY(IF(A2:A = "",, HSTACK(A2:A & "_", B2:B & ",")), "SELECT MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)))

The QUERY function combines values in each column into a single row, considering them all as part of the headers. The TRANSPOSE function changes the orientation of the result.

a_ p1, p2, p3,
b_ p1, p9,
c_ p1, p4, p5,

Step 4:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(QUERY(QUERY(IF(A2:A="",, HSTACK(A2:A&"_", B2:B&",")),"SELECT MAX(Col2) WHERE Col1 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)), "_")), "^,\s|,$", ""))

Finally, we split the text using the underscore delimiter, which is an alternative to VLOOKUP for combining values in Google Sheets. During this process, we use functions like TRIM and REGEXREPLACE to remove extra spaces and delimiters from the start and end of the combined string.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

How to Extract Numbers from Text in Excel with Regex

You can use the REGEXEXTRACT or REGEXREPLACE functions to easily extract numbers from text...

Using OFFSET and MATCH Together in Google Sheets: Advanced Tips

One powerful and flexible way to look up values is by combining the OFFSET...

How to Use OFFSET and XMATCH Functions Together in Excel

We often use the OFFSET and XMATCH functions together to match a value in...

How to Calculate Maximum Drawdown in Excel and Google Sheets

You can use the following dynamic array formula to calculate maximum drawdown (MDD) in...

More like this

Using OFFSET and MATCH Together in Google Sheets: Advanced Tips

One powerful and flexible way to look up values is by combining the OFFSET...

Running Count with Structured References in Google Sheets

Running a count with structured references is achievable in Google Sheets tables using the...

Running Total with Structured Table References in Google Sheets

You can use two types of formulas to create a running total with structured...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.