SOLVED

Return Multiple matches

%3CLINGO-SUB%20id%3D%22lingo-sub-1546678%22%20slang%3D%22en-US%22%3EReturn%20Multiple%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546678%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20assist%20me%20with%20a%20formula%20that%20could%20return%20multiple%20matches%20for%20a%20given%20value.%20I%20have%20noted%20that%20%3CSTRONG%3EThe%20FILTER%3C%2FSTRONG%3E%20function%20gives%20me%20this%20results%20but%20I%20am%20not%20using%20Office%20365.%3C%2FP%3E%3CP%3E1.%20The%20DATA%20ENTRY%20SECTION%20has%20all%20the%20information.%3C%2FP%3E%3CP%3E2.%20The%20report%20summary%20is%20Order%20specific.%20If%20I%20ENTER%20the%20order%20nr.%20in%20cell%20L2%2C%20it%20should%20populate%20all%20the%20cells%20from%20M6%20with%20the%20matching%20values%20for%20order%20nr.%201.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1546678%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546692%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20Multiple%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40AK_ABDI%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20without%20dynamic%20arrays%2C%20in%20M6%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(E%243%3AE%2421%2C%0A%20%20AGGREGATE(15%2C6%2C1%2F(%24D%243%3A%24D%2421%3D%24L%242)*(ROW(%24D%243%3A%24D%2421)-ROW(%24D%242))%2CROW()-ROW(M%245)))%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20to%20the%20right%20till%20end%20of%20the%20range%20and%20after%20that%20entire%20M6%3AP6%20down%20till%20empty%20cells%20appear.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546713%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20Multiple%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546713%22%20slang%3D%22en-US%22%3EThank%20you%20Sergei%20for%20your%20quick%20fix..%20!%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546743%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20Multiple%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40AK_ABDI%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Good day,

 

Please assist me with a formula that could return multiple matches for a given value. I have noted that The FILTER function gives me this results but I am not using Office 365.

1. The DATA ENTRY SECTION has all the information.

2. The report summary is Order specific. If I ENTER the order nr. in cell L2, it should populate all the cells from M6 with the matching values for order nr. 1. 

Thanks for assistance.

3 Replies
best response confirmed by A_SIRAT (Contributor)
Solution

@A_SIRAT 

If without dynamic arrays, in M6

=IFERROR(INDEX(E$3:E$21,
  AGGREGATE(15,6,1/($D$3:$D$21=$L$2)*(ROW($D$3:$D$21)-ROW($D$2)),ROW()-ROW(M$5))),
"")

drag it to the right till end of the range and after that entire M6:P6 down till empty cells appear.

Thank you Sergei for your quick fix.. ! appreciated.

@A_SIRAT , you are welcome