Search code examples
google-sheetsformulaarray-formulas

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.

Solution

  • 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 🍻