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.
I tried removing the wildcard but it also doesn’t work.
| 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:
=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")