Select from drop down and list corresponding column items

[bluedrag]

In Column G of the attached file, I want to select any products and have the Component column list out all the components while showing the corresponding In Stock as well.

[azumi]

Put in G2 and press all together at same time CTRL ALT ENTER button because in an array formula then copied down:

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(COUNTIF($F$3:$F$4,INDEX(LOOKUP(ROW($A$3:$A$13),ROW($A$3:$A$13)/($A$3:$A$13<>""),$A$3:$A$13),,1,1))>0,ROW($B$3:$B$13)-MIN(ROW($B$3:$B$13))+1),ROW(A1)),1),"")

put in H2 and is also an array formula too:

=IFERROR(INDEX($C$3:$C$13,SMALL(IF(COUNTIF($F$3:$F$4,INDEX(LOOKUP(ROW($A$3:$A$13),ROW($A$3:$A$13)/($A$3:$A$13<>""),$A$3:$A$13),,1,1))>0,ROW($B$3:$B$13)-MIN(ROW($B$3:$B$13))+1),ROW(A1)),1),"")

[bluedrag]

Thanks this helped a lot

[azumi]

thanks for the feedback