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
[TABLE="width: 728"]
<colgroup><col span="2"><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Parent[/TD]
[TD][/TD]
[TD]Athlete[/TD]
[TD][/TD]
[TD]Match[/TD]
[TD][/TD]
[TD]Return[/TD]
[/TR]
[TR]
[TD]Bobby Bouche[/TD]
[TD][/TD]
[TD]Billy Bouche[/TD]
[TD][/TD]
[TD="colspan: 2"]Bobby Bouche[/TD]
[TD]Billy Bouche[/TD]
[/TR]
[TR]
[TD]Timmys Dad[/TD]
[TD][/TD]
[TD]Timmy[/TD]
[TD][/TD]
[TD]Timmys Dad[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Johnny Waffles[/TD]
[TD][/TD]
[TD]Eggo Waffles[/TD]
[TD][/TD]
[TD="colspan: 2"]Johnny and Susie Waffles[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Ernie Erns[/TD]
[TD][/TD]
[TD]Sally Erns[/TD]
[TD][/TD]
[TD]Ernie Q. Erns[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Jose Ruiz[/TD]
[TD][/TD]
[TD]Pedro Martinez[/TD]
[TD][/TD]
[TD]Jose Ruiz[/TD]
[TD][/TD]
[TD]Pedro Martinez[/TD]
[/TR]
</tbody>[/TABLE]
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