Index Match: search for a string and return multiple values

%3CLINGO-SUB%20id%3D%22lingo-sub-1330733%22%20slang%3D%22en-US%22%3EIndex%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330733%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI'm%20looking%20to%20do%20a%20lookup%2C%20and%20I%20assume%20index%20match%20is%20the%20rout%20to%20go%2C%20but%20I'm%20not%20certain.%20The%20lookup%20value%20will%20always%20be%20a%20single%20line%2C%20but%20the%20column%20I'm%20searching%20for%20a%20match%20on%20may%20have%20multiple%20lines%20with%20a%20carriage%20return%20between%20them.%20There%20may%20be%20multiple%20matches%20at%20well.%20I've%20attached%20a%20small%20example%20with%20what%20I'm%20hoping%20for%20from%20the%20output.%20I%20tried%20using%20a%20lookup%2Fsearch%20but%20I%20wasn't%20sure%20how%20to%20account%20for%20the%20carriage%20return.%26nbsp%3B%20I%20assume%20Index%2Fmatch%20is%20the%20next%20rout%20to%20go%2C%20but%20I%20wasn't%20sure%20how%20to%20handle%20the%20multiple%20matches.%26nbsp%3B%20Any%20guidance%20would%20be%20greatly%20appreciated!%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1330733%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331040%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633531%22%20target%3D%22_blank%22%3E%40Zexall%3C%2FA%3E%26nbsp%3BHi.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20dataset%20looks%20somehow.%20Why%20do%20you%20have%20several%20items%20in%20each%20cell%20in%20column%20A%3F%20I%20suggest%20each%20cell%20should%20have%20a%20unique%20lookup%20value%20to%20make%20this%20exercise%20easier.%20Is%20this%20the%20only%20way%20the%20report%20can%20be%20generated%20or%20you%20can%20generate%20the%20report%20wit%20each%20item%20in%20each%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331069%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331069%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20love%20the%20way%20you%20used%20the%26nbsp%3B%3CSPAN%3E(SEARCH(%22*%22%26amp%3BD2%26amp%3B%22*%22%2CA2%3AA30)).%20Learnt%20something%20new.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331087%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20How%20do%20you%20resolve%20this%20with%20older%20Excel%20version%20without%20filter%20function%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331104%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3BMuch%20more%20complicated%20in%20old-Excel.%20Key%20would%20be%20to%20do%20the%20ISNUMBER%2FSEARCH%20part%2C%20determine%20the%20row%20numbers%20that%20contain%20the%20%3CSEARCH%20value%3D%22%22%3E%20and%20then%20pick-up%20the%20%3CRESULT%3E%20from%20%3CSTRONG%3Eonly%3C%2FSTRONG%3E%20these%20rows%20using%20the%20INDEX%20and%20COUNT%20and%20AGGREGATE%20functions.%3C%2FRESULT%3E%3C%2FSEARCH%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20Realised%20now%20you%20actually%20don't%20need%20the%20wildcards!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331056%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331056%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633531%22%20target%3D%22_blank%22%3E%40Zexall%3C%2FA%3E%26nbsp%3BThis%20one%20might%20work%20for%20you%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(CHAR(10)%2CTRUE%2CFILTER(B2%3AB30%2CISNUMBER(SEARCH(%22*%22%26amp%3BD2%26amp%3B%22*%22%2CA2%3AA30))%2C%22-%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BEnter%20it%20in%20E2%20and%20copy%20it%20down.%20Make%20sure%20to%20have%20%22Wrap%20text%22%20activated%20where%20this%20formula%20resides.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20%26nbsp%3BForget%20about%20the%20wild-cards%20%22*%22.%20This%20will%20work%20as%20well%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(CHAR(10)%2CTRUE%2CFILTER(B2%3AB30%2CISNUMBER(SEARCH(D2%2CA2%3AA30))%2C%22-%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331927%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20how%20%22old%22%20is%20the%20Excel.%20If%20TEXTJOIN%20is%20supported%20when%20array%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXTJOIN(CHAR(10)%2CTRUE%2C%20IF(ISNUMBER(SEARCH(D2%2CA2%3AA30))%2CB2%3AB30%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eshall%20work%20without%20help%20of%20dynamic%20arrays.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332577%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BCan%20you%20please%20provide%20more%20information%20on%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22%3CEM%3EIf%20TEXTJOIN%20is%20supported%20when%20array%20formula%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHow%20do%20I%20get%20this%20information%2C%20I%20am%20using%20Excel%202019%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1335123%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1335123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20practically%20means%20you%20enter%20such%20formula%20with%20combination%20of%20Ctrl%2BShift%2BEnter%20(instead%20of%20Enter)%2C%20after%20that%20you%20will%20see%20that%20formula%20will%20be%20wrapped%20by%20%7B%7D.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1335628%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%3A%20search%20for%20a%20string%20and%20return%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1335628%22%20slang%3D%22en-US%22%3EI%20get%20your%20point%20now.%20Thanks%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

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

Highlighted

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

 

Highlighted

@Riny_van_Eekelen I love the way you used the (SEARCH("*"&D2&"*",A2:A30)). Learnt something new.

 

 

Highlighted

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

Highlighted

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

Highlighted

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

Highlighted

@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

 

Highlighted

@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 {}.

Highlighted
I get your point now. Thanks