Using IF AND to match three criteria

Question

I am trying to use an IF statement to turn a cell Yes if three conditions are met. It's not working for the third condition. Can anyone look at my formula and point me in the right direction? Note: I can't use array formula because there are other rows in the way.

The three conditions are:

1. The cells in D2:D say User Task
2. The cells in B2:B match the cells in E2:E of another sheet and either C2:C of that sheet say Yes or Reloaded.
3. The cells in B2:B of the other sheet match Customer.

---

=IF(AND(D2:D="User Task",REGEXMATCH(VLOOKUP(B2:B, {
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!E2:E"), 
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!C2:C")}, 2, 0), 
 "Yes|Reloaded")=TRUE,
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!B2:B")="Customer"), "Yes", )

Reference sheets: Test Sheet 2 Test Sheet 1

Answer 1

it goes like this:

=IF( (con1=true)*(con2=true)*(con3=true), do "yes", otherwise empty)

eg:

=ARRAYFORMULA(IFERROR(IF(($D$2:$D="User Task")*(REGEXMATCH(VLOOKUP($B2:B, {
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!E2:E"), 
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!C2:C")}, 2, 0), 
 "Yes|Reloaded")=TRUE)*
 (IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!B2:B")="Customer"), 
 "Yes", )))