Combining Two Arrays into a List

[KPN95]

I have two lookup fields that are an array inside my table. One shows the number of products (30, 30, 30, etc.) and the other show the name of the products (product name 1, product name 2, product name 3, etc.).

If the fields have just one product, I can easily concatenate them into "number" & "name" (e.g., 30 product name 1), but that doesn't work on multiple arrays. I'm trying to combine the fields such that the output will be number and name in order (e.g., 30 product name 1, 30 product name 2).

It seems like ARRAYJOIN() into a list is the appropriate function, but I can't get it to work. The formula of

IF(

  ARRAYJOIN({number}),

  "- " & ARRAYJOIN({number} & {name}, "\n- ")

)

produces "- 3030product name 1product name 2" instead of "- 30 product name 1 - 30 product name 2."

Any ideas?

[Sho · Accepted solution]

ARRAYJOIN only converts arrays to text and does not do what you want.
This is Airtable does not do well.
The formula uses a lookup field, Could you generate a field with "Number" and "Product Name" using a formula field in the lookup table for the "Number"?
It would be good to Lookup and Arrayjoin them.

[KPN95]

This is exactly what I did—thanks for the tip, @Sho. 

In the table that contains the number and name fields, I concatenated those two fields and added the formatting (<li></li> for HTML purposes) and then did the ARRAYJOIN that removed the comma separator on the lookup of the concatenated field in the other table. I just tested it, and it works!