Google Sheets - Concatenating Multiple lists across sheets

Question

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

Answer 1

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.