excelexcel-formula

Excel - how to fix this countif formula

I have a trouble with the COUNTIF formula that I am using. I need to find a specific text in Row C with the value of Row A.

I need to find if any of the cells in row C contains the value of cell A2 which is p1234, however it returns "yes" even with the value of p12345678.

enter image description here

I tried removing the wildcard but it also doesn't work. enter image description here

ColA ColB ColC
p1234 NO Lorem ipsum dolor sit amet, consectetur adipiscing elit p123456789
p5478 YES Lorem ipsum dolor sit amet, consectetur adipiscing elit p5478
p05384 YES Lorem ipsum dolor sit amet, consectetur adipiscing elit p05384
Solution

  • Try using the following formula with SEARCH() function to get the desired output:

    enter image description here

    =IF(SUM(1-ISERR(SEARCH(" "&A2&" "," "&C$2:C$4&" "))),"YES","NO")

    If the search keys are case-sensitive, then can use FIND() in place of SEARCH()

    =IF(SUM(1-ISERR(FIND(" "&A2&" "," "&C$2:C$4&" "))),"YES","NO")

    • Using with ISNUMBER()+FIND()

    =IF(SUM(N(ISNUMBER(FIND(" "&A2&" "," "&C$2:C$4&" ")))),"YES","NO")

    • Or,

    =IF(SUM(N(ISNUMBER(SEARCH(" "&A2&" "," "&C$2:C$4&" ")))),"YES","NO")

    Or, Using BYROW()

    =BYROW(A2:A4,LAMBDA(x,IF(OR(1-ISERR(FIND(" "&x&" "," "&C2:C4&" "))),"YES","NO")))

    • One another way you can do it, using TEXTAFTER() provided the search key is always at the end of the strings to search:

    =IF(OR(1-ISNA(TEXTAFTER(" "&C2:C4&" "," "&A2&" "))),"Yes","No")