How to split comma-separated values across multiple rows dynamically?

Question

I have a table in Google Sheets with products (URLs) in one column and a list of comma-separated colors in another column. I want to transform it into a new table where each product is repeated for every individual color, creating a 'product-variant' pair for each combination.

How can I achieve this dynamically, so if the original table changes, the new table updates automatically? I've tried using formulas like SPLIT and ARRAYFORMULA, but I can't get it to work across multiple rows.

Original Table:

Products	Colors
https://example.com/product/item-1/	Red, Blue, Green, Yellow, Black, White
https://example.com/product/item-2/	Red, Blue, Green, Yellow, Black, White
https://example.com/product/item-3/	Red, Blue, Cyan, Green, Lime Green, Yellow, Magenta, Black, White

Desired Output Table:

Product	Variant
https://example.com/product/item-1/	Red
https://example.com/product/item-1/	Blue
https://example.com/product/item-1/	Green
https://example.com/product/item-1/	Yellow
https://example.com/product/item-1/	Black
https://example.com/product/item-1/	White
https://example.com/product/item-2/	Red
https://example.com/product/item-2/	Blue
https://example.com/product/item-2/	Green
...	...

Answer 1

Here's another solution:

=ARRAYFORMULA(
   QUERY(
     SPLIT(
       TOCOL(A2:A & "❅" & SPLIT(B2:B, ", ", ), 3),
       "❅"
     ),
     "WHERE Col2 IS NOT NULL"
   )
 )