Forum Discussion

Zexall's avatar
Zexall
Copper Contributor
Apr 23, 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 23, 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 23, 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
    Iron Contributor
    Apr 23, 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?

Resources