Searching list of names within a cell

Hi there,

I would need to produce a scorecard for the company. One of the criteria is google review.

What I had done:

Using "Name Manager" to assign all the staff name as StaffName.

Using below formula to search the name within the comment cell

=IFERROR(INDEX(StaffName,MATCH(TRUE,(ISNUMBER(SEARCH(StaffName,T2,1))),0),1),"")

Above formula got me the name and I perform count if to find out how many comments each staff got.

The problem I'm facing is there are some comments mentioning multiple names. For example "Mary, Peter, John all did a great job"

The result I'm looking for is Mary, Peter and John all gets one count for this comment. How can I achieve that?

Thanks in advance for your time and patience.

Bowen

Using 365

= LET(
    isMentioned, SIGN(ISNUMBER(SEARCH(staffName, TOROW(comments),1))),
    BYROW(isMentioned, LAMBDA(crit, SUM(crit)))
  )

Using legacy Excel

= MMULT(
    SIGN(ISNUMBER(SEARCH(staffName,TRANSPOSE(comments),1))),
    SIGN(ISTEXT(comments))
  )

entered with CSE.

Forum Discussion