If Cell Contains Any Words Found in the Adjacent Cell

Please point me in the right direction. I don't know which formula(s) to use to get this output:

If A2 matches to any of the abbreviated roles in B2 then "Match" if not "No Match"

Not an exact match, but as long as column B has a minimum of 1 match, then it is a match.

A	B
Role	VerifyRole
PC	PC, OD, PO	Match
PO	PO, PO	Match
OD	PSS, OD	Match
PSS	PO, PSS	Match
OD, PC	PC, OD	Match
OD, PC	PO, PSS	No Match
OD, PO	OD, PC, PC, PO	Match

I am editing this message because I thought I found the answer. I was wrong.

can you use VBA?

Maybe you could try this as well

Code:

---

Private Sub CheckText()
Dim Partial_text As String
Dim myrange As Range
Partial_text = Worksheets("Sheet1").Cells(1, 2).Value
Set myrange = Worksheets("Sheet1").Cells(2, 2).Value
For Each cell In myrange
  If InStr((cell.Value), (Partial_text)) <> 0 Then
      ActiveCell(Offset(0, 1).Value) = "Match"
      Else
      ActiveCell(Offset(0, 1).Value) = "No Match"
  End If
Next
End Sub

---

Aussiebear, this also worked. Thank you. :friends:

arnelgp yes and this worked. Thank you. :friends:

Another way would use a User Defined Function which would be more flexible

Attachment 30728

Code:

---

Option Explicit


Function VerifyRole(Role As String, Verify As String) As String
    Dim vR As Variant, vV As Variant
    Dim i As Long, j As Long
   
    VerifyRole = "Match"
   
    vR = Split(Role, ",")
    vV = Split(Verify, ",")


    For i = LBound(vR) To UBound(vR)
        For j = LBound(vV) To UBound(vV)
            If Trim(vR(i)) = Trim(vV(j)) Then Exit Function
        Next j
    Next i


    VerifyRole = "NoMatch"


End Function

---

also this one:

Code:

---

Public Function fnIsMatched(ByVal a As Variant, b As Variant) As Boolean
Dim var1 As Variant, var2 As Variant
Dim i As Integer, tf As Boolean
If IsNull(a) Or IsNull(b) Then
    Exit Function
End If
a = Replace$(a, " ", "")
b = Replace$(b, " ", "")
var1 = Split(a, ",")
var2 = Split(b, ",")
For i = 0 To UBound(var1)
    tf = UBound(Filter(var2, var1(i))) > -1
    If tf Then
        Exit For
    End If
Next
fnIsMatched = tf
End Function

---

An Excel 365 formulated option:

Code:

---

=LET(a,BYROW(A2:B8,LAMBDA(x,SUM(SEARCH(TEXTSPLIT(INDEX(x,,1),", "),INDEX(x,2))))),IF(ISNUMBER(a),"Match","No Match"))

---