Search code examples
excelvbaloopsvlookup

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

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!

Solution

  • 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",

    enter image description here