Filter string in array formula

[fhceq]

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

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

Thanks for considering my request!

[#2]

Hi @fhceq 

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

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

[#3]

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

[#4 · Accepted solution]

@fhceq 

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