If Cell Contains Any Words Found in the Adjacent Cell

[jnix612]

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

[jnix612]

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

[arnelgp]

can you use VBA?

[Aussiebear]

Maybe you could try this as well



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

[jnix612]

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

[jnix612]

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

[Paul_Hossler]

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

30728




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

[arnelgp]

also this one:


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

[georgiboy]

An Excel 365 formulated option:

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