Forum Discussion

Bowen0520's avatar
Bowen0520
Copper Contributor
Dec 05, 2023
Solved

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 belo...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Dec 05, 2023

    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.

     

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 05, 2023

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.

 

 

Resources