A Flexible Array Formula for Joining Columns in Google Sheets

If you’re looking for a flexible array formula to join columns in Google Sheets, you’re in the right place! I have two of the best solutions to share. We can join columns using a simple Query formula.

Typically, we rely on the JOIN, TEXTJOIN, CONCATENATE, CONCAT functions, and the ampersand to combine values. However, none of these fully support arrays in the way we need.

The first three are not suitable for combining entire columns, as they return a single value instead of working row by row.

The CONCAT function can combine columns but lacks flexibility, as it only supports two arrays and doesn’t allow for a delimiter.

The ampersand is a better option, though it’s not dynamic. You must specify each column individually.

But I have a simple and elegant solution using a Query that works with both physical and virtual columns.

Update: We can now use JOIN and TEXTJOIN functions with a supporting Lambda helper function to achieve the desired results.

Joining Columns Using JOIN, TEXTJOIN, and CONCATENATE Functions

We’ll start with drag-down formulas to combine columns.

We’ll use a sample data set that contains first names in A2:A11 and last names in B2:B11, and we’ll combine them in C2:C11.

Sample Data: Array Formula for Joining Multiple Columns in Google Sheets

Insert any of the following formulas in cell C2 and drag it down as far as needed:

=JOIN(" ", A2:B2)
=CONCATENATE(A2, " ", B2)
=TEXTJOIN(" ", TRUE, A2:B2)

In all three formulas above, the space is the delimiter that separates the first and last names.

The TEXTJOIN function has an added advantage: it won’t add the delimiter if you forget to enter the first or last name. This ensures that the output looks clean, especially when the delimiter is not a space but a comma, pipe, or another character.

We can’t use the CONCAT function, as it doesn’t have the option to place a delimiter—it simply combines two columns.

All of the above are non-array formulas for joining columns. However, we can convert them to flexible array formulas using the BYROW and LAMBDA functions, which we’ll explore later.

Note: If you have three columns, the formulas will look as follows:

=JOIN(" ", A2:C2)
=CONCATENATE(A2, " ", B2, " ", C2)
=TEXTJOIN(" ", TRUE, A2:C2)

Array Formula Approach Using Ampersand (Non-Flexible)

I might recommend using the ampersand if you ask me which is the easiest array formula method to join columns in Google Sheets.

=ArrayFormula(A2:A11&" "&B2:B11)
Ampersand: The Simple Array Formula for Joining Texts

There is one drawback:

  • You need to specify the columns individually, not as a range, making it non-flexible.

If you have one more column, the formula would look something like this:

=ArrayFormula(A2:A11&" "&B2:B11&" "&C2:C11)

Flexible Array Formula to Join Columns in Google Sheets – QUERY

First, let’s look at the formula:

=TRANSPOSE(QUERY(TRANSPOSE(A2:B11), , COLUMNS(A2:B11)))

This is called a flexible array formula because it uses a range instead of specifying columns individually to join columns.

Anatomy of the Formula:

Here’s the syntax: QUERY(data, query, [headers])

If you use the QUERY function without a query parameter, it will return the data as is.

In our Query, the transposed range A2:B11 is the data:

=QUERY(TRANSPOSE(A2:B11))
QUERY Function with Transposed Data

We have transposed two columns into two rows. We need to tell the Query that these two rows are headers:

=QUERY(TRANSPOSE(A2:B11), , 2)

Result:

QUERY Function to Merge Headers in Google Sheets

Instead of hardcoding the number 2, we use the COLUMNS function to dynamically return the number of columns in the data range A2:B11 (which corresponds to the number of rows in the transposed data range A2:B11).

That’s what I did—then I transposed the data once again.

Flexible Array Formula for Joining Columns in Google Sheets Using BYROW

Using BYROW, one of the Lambda helper functions, we can enhance the results of the text functions mentioned earlier in this tutorial. I am choosing TEXTJOIN because it can omit blank cells.

Here is the next flexible array formula to join columns in Google Sheets:

=BYROW(A2:B11, LAMBDA(r, TEXTJOIN(" ", TRUE, r)))

The unnamed LAMBDA function joins the first and last names using TEXTJOIN, and BYROW iterates over each row in the array, making it a dynamic array formula.

Which Dynamic Formula Is Right for Me?

I suggest using QUERY if you have very large data and want a space as the column separator. If you need to control the delimiter and achieve a clean output, BYROW is the best option.

However, BYROW is a LAMBDA helper function that might encounter performance issues with very large data sets.

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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

7 COMMENTS

  1. Is there a way to insert a unique delimiter between the penultimate and last values for each row?

    What I am trying to do is add an “and” before the last value in a comma-separated list.

  2. This is what I needed! In the last formula you used, how can I put a separator between the combined values?

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.