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

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 24, 2020
  I get your point now. Thanks
- wumolad
  Iron Contributor
  Apr 23, 2020
  SergeiBaklan 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
  - SergeiBaklan
    Diamond Contributor
    Apr 24, 2020
    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 {}.