INDEX,MATCH Multiple exact words from cells with variances.

[steveperry]

Hi,

I'm trying to create an index/match type formula that will match multiple exact words in cells that contain those words as well as variances such as double spacing, extra letters, extra words, and variable orders of the words. I've been scouring for answers without any real success. Any help would be greatly appreciated!

Example:
C D E F

Parent		Athlete		Match		Return
Bobby Bouche		Billy Bouche		Bobby Bouche	Billy Bouche
Timmys Dad		Timmy		Timmys Dad		#N/A
Johnny Waffles		Eggo Waffles		Johnny and Susie Waffles	#N/A
Ernie Erns		Sally Erns		Ernie Q. Erns		#N/A
Jose Ruiz		Pedro Martinez		Jose Ruiz		Pedro Martinez

<colgroup><col span="2"><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

This is the formula I'm using in the return column F.

=INDEX($D$2:$D$6,MATCH(E3,$C$2:$C$6,0))

How can I match the parents despite the variances to return the athlete. I can use the "Trim" function to erase the double spaces, but I wouldn't mind having something that is all-encompassing.

Thanks for your help, it is much appreciated!

-Steve

 

[decadence]

Perhaps This...

Sub ExactWords()
    Dim c As range, Rng As range, x As range
    Dim i As Long, LR As Long
    Dim Arr As Variant
    Dim ConvertNonBreakingSpace As String, s As String
    Application.ScreenUpdating = False
    On Error Resume Next
    Set Rng = ActiveSheet.UsedRange
    Arr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
    For Each x In Rng
        s = x
            If ConvertNonBreakingSpace Then s = replace(s, Chr(160), " ")
                For i = LBound(Arr) To UBound(Arr)
                    s = replace(s, Chr(Arr(i)), "")
                Next
            x = Application.WorksheetFunction.Trim(s)
        Next
    LR = range("C" & Rows.Count).End(xlUp).Row
    For Each c In range("C2:C" & LR)
        If c.Value Like c.Offset(, 2).Value Then
            c.Offset(0, 3).Value = c.Offset(0, 1).Value
        Else
            c.Offset(, 3).Value = "#N/A"
        End If
    Next c
    Application.ScreenUpdating = True
End Sub