SOLVED

Filter string in array formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3105174%22%20slang%3D%22en-US%22%3EFilter%20string%20in%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3105174%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3EPlease%2C%20I'm%20dealing%20with%20a%20situation%20I'm%20having%20trouble%20solving.%3C%2FP%3E%3CP%3EIn%20table%201%2C%20there%20are%20a%20few%20products%20in%20column%20A%20and%20raw%20materials%20associated%20with%20the%20production%20of%20those%20products%20in%20column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3EReading%20some%20forums%2C%20I%20came%20up%20with%20this%20function.%20However%2C%20I%20was%20not%20able%20to%20filter%20some%20raw%20materials%20associated%20with%20the%20product%20I%20don't%20want%20to%20be%20shown.%3C%2FP%3E%3CP%3EWhen%20I%20choose%20the%20product%2C%20and%20I'd%20like%20that%20some%20raw%20materials%20related%20to%20that%20product%20are%20shown%20but%20some%20aren't%2C%20and%20it%20needs%20to%20be%20dynamic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(%24B%245%3A%24B%2419%3BSMALL(IF(%24A%245%3A%24A%2419%3D%24E%242%3BROW(%24B%245%3A%24B%2419)-MIN(ROW(%24B%245%3A%24B%2419))%2B1)%3BROWS(%24E%245%3AE5)))%3B%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Captura%20de%20tela%202022-02-02%20105746.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344593iC378F099F5AD55A6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Captura%20de%20tela%202022-02-02%20105746.png%22%20alt%3D%22Captura%20de%20tela%202022-02-02%20105746.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EIt%20is%20important%20the%20formula%20runs%20on%20Office%202016%20and%20it%20does%20not%20use%20VBA%20or%20Power%20Query.%3C%2FSTRONG%3E%3C%2FP%3E%3CH2%20id%3D%22toc-hId-358691115%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%20id%3D%22toc-hId-358691148%22%3E%3CFONT%20size%3D%223%22%3EThanks%20for%20considering%20my%20request!%3C%2FFONT%3E%3C%2FH2%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3105174%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3105620%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20string%20in%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3105620%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1285474%22%20target%3D%22_blank%22%3E%40fhceq%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20data%20in%26nbsp%3B%3CEM%3E%3CSTRONG%3ETable1%3C%2FSTRONG%3E%3C%2FEM%3E%20(you%20can%20convert%20to%20range%20if%20you%20prefer)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot.png%22%20style%3D%22width%3A%20384px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344619i16BFD48D4EC0E18B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot.png%22%20alt%3D%22Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3ED6%3C%2FSTRONG%3E%20and%20copy%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20%20INDEX(Table1%5BRaw%20Material%5D%2C%0A%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C(ROW(Table1)-ROW(Table1%5B%23Headers%5D))%2F(Table1%5BProducts%5D%3DE%242)%2CROWS(%2410%3A10))%0A%20%20%20%20)%0A%20%20%20%20%2C%22%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3113129%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20string%20in%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113129%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%2C%20thank%20you%20so%20much%20for%20your%20help.%3CBR%20%2F%3EBut%20I%20still%20don't%20know%20how%20to%20make%20some%20raw%20materials%20not%20to%20be%20shown%20in%20the%20results%20table.%20Using%20your%20example%2C%20if%20I%20don't%20want%20that%20A3%20does%20not%20apear%20in%20the%20results%20when%20I%20choose%20Product%20A%3B%20or%20B3%20when%20I%20choose%20product%20B%2C%20how%20can%20I%20do%20it%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3113329%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20string%20in%20array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1285474%22%20target%3D%22_blank%22%3E%40fhceq%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345137i460D3656E7BB8B96%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot.png%22%20alt%3D%22Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EH5%3C%2FSTRONG%3E%20and%20copy%20down%20as%20necessary%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20%20INDEX(Table1%5BRaw%20Material%5D%2C%0A%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(%20ROW(Table1)-ROW(Table1%5B%23Headers%5D)%20)%20%2F%0A%20%20%20%20%20%20%20%20%20%20%20%20(%20(Table1%5BProducts%5D%3DE%242)%20*%20ISNA(MATCH(Table1%5BRaw%20Material%5D%2CE%244%3AE%2412%2C0))%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%2410%3A10)%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%2C%22%22%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20range%20E%244%3AE%2412%20contains%20the%20%5BRaw%20Material%5D%20you%20want%20to%20exclude%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear all,

Please, I'm dealing with a situation I'm having trouble solving.

In table 1, there are a few products in column A and raw materials associated with the production of those products in column B. 

Reading some forums, I came up with this function. However, I was not able to filter some raw materials associated with the product I don't want to be shown.

When I choose the product, and I'd like that some raw materials related to that product are shown but some aren't, and it needs to be dynamic.

 

=IFERROR(INDEX($B$5:$B$19;SMALL(IF($A$5:$A$19=$E$2;ROW($B$5:$B$19)-MIN(ROW($B$5:$B$19))+1);ROWS($E$5:E5)));"")

 

Captura de tela 2022-02-02 105746.png

It is important the formula runs on Office 2016 and it does not use VBA or Power Query.

Thanks for considering my request!

3 Replies

Hi @fhceq 

 

With data in Table1 (you can convert to range if you prefer)

 

Screenshot.png

in D6 and copy down:

=IFERROR(
    INDEX(Table1[Raw Material],
        AGGREGATE(15,6,(ROW(Table1)-ROW(Table1[#Headers]))/(Table1[Products]=E$2),ROWS($10:10))
    )
    ,""
)

Sample attached

@L z., thank you so much for your help.
But I still don't know how to make some raw materials not to be shown in the results table. Using your example, if I don't want that A3 does not apear in the results when I choose Product A; or B3 when I choose product B, how can I do it?
best response confirmed by fhceq (Occasional Contributor)
Solution

@fhceq 

 

Screenshot.png

in H5 and copy down as necessary

=IFERROR(
    INDEX(Table1[Raw Material],
        AGGREGATE(15,6,
            ( ROW(Table1)-ROW(Table1[#Headers]) ) /
            ( (Table1[Products]=E$2) * ISNA(MATCH(Table1[Raw Material],E$4:E$12,0)) ),
            ROWS($10:10)
        )
    ),""
)

where range E$4:E$12 contains the [Raw Material] you want to exclude