Excel: Lookup multiple values and list them in a single cell

Question

Let's say I have an Excel table listing books by authors in column A and B. In columns "Adam" and "Alice" a book gets an "x" if a character with that name occurs in the book.

Column A	Column B	Adam	Alice
Title1	Author1		x
Title2	Author2	x
Title3	Author1	x

In another table I have a column with all names (i.e. Adam and Alice). I now want to find all the books that contains the specific name and list them in a single cell with commas, like so:

Names	In books
Adam	Title2 by Author2, Title3 by Author1
Alice	Title1 by Author1

I am not quite sure how to approach this problem. Do I have to use VBA or could I use nested functions? I thought I could use MATCH and perhaps TEXTJOIN() inside some kind of IF function or loop, but I don't know how. Help!

Answer 1

Filter Excel Table Data

MS365

=IFNA(TEXTJOIN(", ",,FILTER(
    Table1[Titles]&" by "&Table1[Authors],
    CHOOSECOLS(Table1,XMATCH([@Names],Table1[#Headers]))="x",
    "No titles!")),"Not listed!")

For Excel 2021, replace CHOOSECOLS(Table1,XMATCH([@Names],Table1[#Headers]))="x", with

INDEX(Table1,0,XMATCH([@Names],Table1[#Headers]))="x",        

For Excel 2019, replace CHOOSECOLS(Table1,XMATCH([@Names],Table1[#Headers]))="x", with

INDEX(Table1,0,MATCH([@Names],Table1[#Headers],0))="x",