Index, Small, Ifs, Row and Rows to extract values

[bjnockle]

Looking for index, small, Ifs, Row and Rows to extract values in Product sheet into Outcome Sheet if condition meet. In B2 of Outcome sheet, there is a list of product options. When a product is selected in B2 (Outcome sheet), need formula to extract a match in A2:A20 on Product Sheet. Formula to take into consideration F2:I20 product values.

Example, B2 (Outcome sheet) = Alicante, Bouschet
Formula to look into F2:I20 on Product sheet and extract instances of Alicante, Bouschet in A2:A20. See sample formula (not working) - needs tweaking.

=IF($B$1="","",IFERROR(INDEX(Products!$A$2:$A$20,SMALL(IF((Products!$F$2:$F$20=Outcome!$B$1)*(Products!$G$2:$G$20=Outcome!$B$1)*(Products!$H$2:$H$20=Outcome!$B$1)*(Products!$I$2:$I$20=Outcome!$B$1),ROW(Products!$A$2:$A$20)),ROWS(Outcome!A$4:A4))-ROW(Products!$A$2)+1),""))

Sample file attached

[Phuocam]

Try this ...

=IF($B$1="","",IFERROR(INDEX(Products!$A:$A,SMALL(IF(ISNUMBER(SEARCH($B$1,Products!$B$2:$B$20)),
ROW(Products!$B$2:$B$20)),ROWS(Outcome!A$4:A4))),""))

Enter with Ctrl+Shift+Enter.

[bjnockle]

Phuocam: Adjusted Products!$A:$A to Products!$A2:$A20 and did not pull the correct product origin. Instead of Products!$A:$A, would like Products!$A:$A to start at Products!$A2:$A20. Please modify.

Thanks

[Tony Valko]

Adjusted Products!$A:$A to Products!$A2:$A20 and did not pull the correct product origin. Instead of Products!$A:$A, would like Products!$A:$A to start at Products!$A2:$A20.

Do not change A:A to A2:A20.