Q:

ArrayFormula retrieve list by multiple criterias

Input
Data sheet
TaskId  ClientId Canceled
1             1         0
2             1         0
3             1         0
4             2         0
5             2         1
6             2         0
7             3         0

Report sheet
ClientId
1
1
2
3

Desired Output
Arrayformula to get all TaskIds from Data by clients where Canceled = 0
TaskIds
1
2
3
1
2
3
4
6
7

I have join + filter formula to drag down, which gives me all TaskIds for clients:
ClientId  TaskIds
1          1,2,3
1          1,2,3
2          4,6
3          7

Then I get my result from this helper_column:
=transpose(split(join(",", helper_colum)))
And I want to make this work without need to drag down.

A:

Try this:
  =ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(""&TRANSPOSE(IF(TRANSPOSE(A11:A14)=B2:B8,IF(C2:C8=0,A2:A8,""),""))),"")))

A11:A14=Report sheet Client ID.
A2:C8=Data sheet values.
Cheers 

