Google Sheets: How to combine 2 ranges in 1 column

google sheets

I am looking for a way to combine 2 / potentially more columns into 1 column, where all values (text) of the source columns are combined with each other (covering all possible combinations of the values from the 2 columns):

Column 1 Column 2 Column 3
Value 1a Value 2a Value 1a + Value 2a
Value 1b Value 2b Value 1b + Value 2a
Value 1c Value 2c Value 1c + Value 2a
Value 1a + Value 2b
Value 1b + Value 2b
Value 1c + Value 2b
Value 1a + Value 2c
Value 1b + Value 2c
Value 1c + Value 2c

Best Answer

You can create a Cartesian product like the one you describe with string concatenation like this:

=arrayformula( flatten( transpose(A2:A4) & " " & B2:B4 ) )