Searching list of names within a cell

[Bowen0520]

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

[#2]

@Bowen0520

Could you make a sample workbook (without sensitive data) available via OneDrive, Google Drive, Dropbox or similar?

[#3 · Accepted solution]

@Bowen0520 

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.

[#4]

A big THANK YOU for @PeterBartholomew1!