I have this table

I am trying to find a formula that will return all the people from column A that have 0530 in column B. So my output in a single cell would be Kelly, Jenn. I've tried several things on my own, but nothing seems to be working. Can anyone help?

I've tried

=ARRAYFORMULA(TEXTJOIN("",FALSE,IF(D2:D8="0530",C2:C8,"no")))

but everything is a "no" because the cells don't match exactly.

CodePudding user response：

Try this

=TEXTJOIN(", ",1,FILTER(A2:A,REGEXMATCH(B2:B, "0530")=TRUE))

Filter A column if B column matches "0530" and join the result with textjoin ignore_empty set to 1.

Used formulas help

CodePudding user response：

try (simply):

=TEXTJOIN(", ", 1, IF(ISNUMBER(SEARCH("0530", $B:$B)), $A:$A, ""))