Google Sheets - Concatenating Multiple lists across sheets

1
votes

I am interested in taking the values from 1 list in a sheet and concatenating it with the values from another list in a different sheet in Google Sheets. For example, if my lists are:

Sheet A

  • Apple
  • Orange

Sheet B

  • Quarter
  • Half
  • Whole

I would want to generate on Sheet C this...

  • Apple Quarter
  • Apple Half
  • Apple Whole
  • Orange Quarter
  • Orange Half
  • Orange Whole

Is this possible? I tried doing this with ARRAYFORMULA, but I just end up outputting something like this: Apple QuarterHalfWhole

1
google-sheetsgoogle-sheets-formula

1 Answers

3
votes

Here you go:

=ARRAYFORMULA(FLATTEN(A1:A2 & " " & TRANSPOSE(B1:B3)))
  • A1:A2 and B1:B3 indeed could be from a different sheet (tab or document (IMPORTRANGE will be used for that case))
  • FLATTEN is undocumented function in Google Sheets, which will make a 2D-range into a column. I learned about it recently from @MattKing here on SO.

enter image description here