How to create a formula that will repeat a range by splitting one column and joining with the other column?

Question

I have 2 columns, first name and surname. I want to split a cell that contains multiple values and then combine it with the cell on the right. I have no idea how to do this using a formula, please help.

Before:

First Name	Surname
John,Jane,Mary	Fish
Albert,Steven,Alice	Smith

Expected Result:

First Name	Surname
John	Fish
Jane	Fish
Mary	Fish
Albert	Smith
Steven	Smith
Alice	Smith

Answer 1

use:

=INDEX(QUERY(SPLIT(FLATTEN(IF(IFERROR(
 SPLIT(A1:A, ","))="",,SPLIT(A1:A, ",")&"​"&B1:B)), "​"), 
 "where Col2 is not null", ))

Answer 2

You can do this by looping over the range twice. First, loop over the range with REDUCE. Then SPLIT each of column A, then loop over each split of Column A and create a dynamic array using array literals: {}

=REDUCE(
  A1:B1,
  A2:INDEX(A2:A,COUNTA(A2:A)), 
  LAMBDA(a,c, 
    {
      a;
      REDUCE(
        "",
        SPLIT(c,","),
        LAMBDA(
          a_,
          c_,
          IF(
            a_="",
            {c_,OFFSET(c,0,1)},
            {a_;{c_,OFFSET(c,0,1)}}
          )
        )
      )
    }
  )
)

First Name	Surname
John	Fish
Jane	Fish
Mary	Fish
Albert	Smith
Steven	Smith
Alice	Smith