Search of multiple values connecting multiple tables

ema_rz 

Having been, shall we say, somewhat discouraging, I used Riny_van_Eekelen 's setup to scan through the array of tables, picking up a selection at each stage.  I always searched column 2 to return column 1 (array calculation has problems with irregular patterns, so I switched columns in the second table).

I used REDUCE to scan the tables by index and generate the output selection from each input.

= IFERROR(
    TEXTJOIN(", ",, 
      SORT(
        REDUCE(Selection,{1,2,3,4}, LAMBDA(tgt,tbl, NextTargetλ(tgt, tbl)))
      )
    ),
  "")

I was surprised that it worked.  The complexity came when I needed to trap errors that occurred when there were no matches.  

NextTargetλ = LAMBDA(tgt, tblNum,
    DROP(
        UNIQUE(
            REDUCE("",tgt,
                LAMBDA(list,t,
                    LET(
                        returnlist,TblColλ(tblNum, 1),
                        lookupList,TblColλ(tblNum, 2),
                        IF(
                            AND(t<>"?",OR(lookupList=t)),
                            VSTACK(list,FILTER(returnlist, lookupList=t,"?"),
                            list)
                        )
                    )
                )
            ),
        ),
    1)
);

Now I suppose I should do the decent thing and ensure Riny's solution works with my altered data.  

[p.s.  I relinked the joins but, oddly, I finished up with a situation in which Refresh resets the selection to "Sophie"]