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"]

ema_rz 

What a revolting problem.  Are you sure you need to solve it?

To iterate through a sequence of Tables you would need to be able to address each Table by index, say = TblColλ(1,2), where the Lambda function is defined by

= LAMBDA(tbleNum,fieldNum,
      CHOOSECOLS(CHOOSE(tbleNum,Table1,Table2),fieldNum)
  )

(note the presence of a beta release function).

Then you would need a further Lambda function = NextTargetλ(target,k), which, given the list of valid target values for Table k would produce a list of valid output values

= LAMBDA(tgt,tblNum,
      DROP(REDUCE("", tgt,
         LAMBDA(list,t,
            VSTACK(list, FILTER(TblColλ(tblNum,1),TblColλ(tblNum,2)=t)))
         ),
      1)
   )

Even then, you would need to provide a further REDUCE formula to step through the Tables and cater for the fact that some of your tables have the columns in reverse order.  Concatenating the result, if you ever got there, would be the easy part!

Unless someone is paying you a large sum of money for this, I would recommend finding some other way of spending you life.  I attach a file with outline ideas.

Here's an example of how it should work.