This would definitely require VBA. The following function will return a value from 0-1 (i.e. a percentage) of values that match **in the same position**. Use this the same as you would use a built-in function. You could then apply normal conditional formatting to this value.

It has an optional True/False (default false) of whether it should be case-sensitive.

"Bob ate Dinner" vs "Bob ate Dinner." would be 0.66, as it does not ignore punctuation.

"Bob Bob Bob Bob" vs "Bob Bob" = 2/4 = 0.5--when strings contain different number of words, the bigger of the two is the denominator, but your numerator maxes out at the smaller of the two.

```
Function ScorePair(stringInput As String, stringTarget As String, Optional caseSensitive As Boolean = False) As Double
Dim scoreNum As Integer
Dim scoreDen As Integer
Dim splitInput As Variant
Dim splitTarget As Variant
Dim theScore As Double
Dim sizeTarget As Integer
Dim sizeInput As Integer
Dim loopSize As Integer
'Initialize
scoreNum = 0
i = 0
'Extract strings into arrays of words
splitInput = Split(stringInput, " ")
splitTarget = Split(stringTarget, " ")
'Get sizes of arrays to know how much to loop (smaller of two) and what to set denominator at (bigger of two)
sizeInput = UBound(splitInput, 1)
sizeTarget = UBound(splitTarget, 1)
scoreDen = WorksheetFunction.Max(sizeTarget, sizeInput) + 1
loopSize = WorksheetFunction.Min(sizeTarget, sizeInput)
'Loop through arrays comparing them by matching position
For i = i To loopSize
If caseSensitive Then
If splitInput(i) = splitTarget(i) Then
scoreNum = scoreNum + 1
End If
ElseIf LCase(splitInput(i)) = LCase(splitTarget(i)) Then
scoreNum = scoreNum + 1
End If
Next
'Calculate the score as percentage
theScore = scoreNum / scoreDen
ScorePair = theScore
End Function
```

`B2`

that are found in`A2`

? – Excellll May 14 '14 at 20:59