Forum Discussion

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 multip...

lookup sample.xlsx11 KB

excel

Formulas and Functions

office 365

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

wumolad
Iron Contributor
Apr 23, 2020
Riny_van_Eekelen How do you resolve this with older Excel version without filter function?
- 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!
  - SergeiBaklan
    Diamond Contributor
    Apr 23, 2020
    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.
wumolad
Iron Contributor
Apr 23, 2020
Riny_van_Eekelen I love the way you used the (SEARCH("*"&D2&"*",A2:A30)). Learnt something new.