Index Match: search for a string and return multiple values

Copper Contributor

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!  

9 Replies

@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?

@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 I love the way you used the (SEARCH("*"&D2&"*",A2:A30)). Learnt something new.

 

 

@Riny_van_Eekelen  How do you resolve this with older Excel version without filter function?

@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!

@Riny_van_Eekelen 

It depends on how "old" is the Excel. If TEXTJOIN is supported when array formula

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

shall work without help of dynamic arrays.

@Sergei Baklan Can you please provide more information on this:

 

"If TEXTJOIN is supported when array formula"

 

How do I get this information, I am using Excel 2019

 

@wumolad 

That practically means you enter such formula with combination of Ctrl+Shift+Enter (instead of Enter), after that you will see that formula will be wrapped by {}.

I get your point now. Thanks