Forum Discussion

Zexall's avatar
Zexall
Copper Contributor
Apr 22, 2020

Index Match: search for a string and return multiple values

I'm looking to do a lookup, and I assume index match is the rout to go, but I'm not certain. The lookup value will always be a single line, but the column I'm searching for a match on may have multiple lines with a carriage return between them. There may be multiple matches at well. I've attached a small example with what I'm hoping for from the output. I tried using a lookup/search but I wasn't sure how to account for the carriage return.  I assume Index/match is the next rout to go, but I wasn't sure how to handle the multiple matches.  Any guidance would be greatly appreciated!  

excel
Formulas and Functions
office 365

9 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 22, 2020

    Zexall This one might work for you:

     

    =TEXTJOIN(CHAR(10),TRUE,FILTER(B2:B30,ISNUMBER(SEARCH("*"&D2&"*",A2:A30)),"-"))

     

     Enter it in E2 and copy it down. Make sure to have "Wrap text" activated where this formula resides.

     

    EDIT:  Forget about the wild-cards "*". This will work as well:

    =TEXTJOIN(CHAR(10),TRUE,FILTER(B2:B30,ISNUMBER(SEARCH(D2,A2:A30)),"-"))

     

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Apr 22, 2020

        wumolad Much more complicated in old-Excel. Key would be to do the ISNUMBER/SEARCH part, determine the row numbers that contain the <search value> and then pick-up the <result> from only these rows using the INDEX and COUNT and AGGREGATE functions.

         

        Edit: Realised now you actually don't need the wildcards!

  • wumolad's avatar
    wumolad
    Steel Contributor
    Apr 22, 2020

    Zexall Hi.

     

    The dataset looks somehow. Why do you have several items in each cell in column A? I suggest each cell should have a unique lookup value to make this exercise easier. Is this the only way the report can be generated or you can generate the report wit each item in each cell?