Apr 22 2020 05:24 PM
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!
Apr 22 2020 09:10 PM
@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?
Apr 22 2020 09:35 PM - edited Apr 22 2020 10:21 PM
@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)),"-"))
Apr 22 2020 09:51 PM
Apr 22 2020 10:00 PM
@Riny_van_Eekelen How do you resolve this with older Excel version without filter function?
Apr 22 2020 10:15 PM - edited Apr 22 2020 10:19 PM
@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!
Apr 23 2020 04:39 AM
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.
Apr 23 2020 08:19 AM
@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
Apr 24 2020 01:55 AM
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 {}.
Apr 24 2020 06:42 AM