Auto Fill formula using conditional formatting across tabs

Try the following:
1) Select Stock Take G6,
2) Paste the following array entered formula* into the formula bar:

Formula:

=IFERROR(INDEX('Product Sales'!B$6:B$17,SMALL(IF('Product Sales'!D$6:D$17="AC",ROW($6:$17)-5),ROW(1:1))),"")

3) *While G6 is still in edit mode simultaneously press the Ctrl, Shift and Enter keys,
4) Copy down.
5) Select Stock Take H6,
6) Paste the following formula into the formula bar:

Formula:

=IFERROR(INDEX('Product Sales'!B$6:B$17,SMALL(IF('Product Sales'!D$6:D$17="AC",ROW($6:$17)-5),ROW(1:1))),"")

7) Press the Ctrl and Enter keys,
8) Double click the fill handle to copy down.
Let us know if you have any questions.