Excel Range IF fails with an AND

Trying to aggregate a list of names that are overdue. This works great until I add an AND to the IF, then everything falls apart and no results are returned.

I need to aggregate a list of names by each person that are overdue.

TEXTJOIN(
  ", ", 
  TRUE, 
  IF(
    AND(
      H2=$B$2:$B$8,
      $C$2:$C$8<NOW()
    ),
    $A$2:$A$8,
    ""
    )
  )

Any suggestions?

Thanks

Try using the following formula:

=TEXTJOIN(", ", TRUE, IF((H2=$B$2:$B$8)*($C$2:$C$8<NOW()), $A$2:$A$8, ""))

Can also use one single dynamic array formula to return the entire output here:

=MAP(E3:E6, LAMBDA(_x, 
 TEXTJOIN(", ", , FILTER(A3:A9, 
                        (_x=B3:B9)*
                        (C3:C9<TODAY()), 
                        "Oops Not Found"))))

Note: So normally you can't really use OR() or AND() in array formulas the way you might expect, like you've noticed, they only spit out a single result. AND() only gives you TRUE if every condition is TRUE, and OR() just needs one to be TRUE. It's not built to evaluate row by row in an array, which is where it trips people up. That when if you ever came across that you have to use an OR() then use OR operator + similar like using, AND operator for AND() function *