Formula needed to find best match

Please see the attached sheet.

I want a formula to find the best (closest) match within a list and return and item code.

Been searching all over the internet and haven't had any luck.

It probably has to be a pretty complex formula for example i want to return "COKE DIET FREE" if the string is "Caffeine Free Diet Coke" or "Caffeine Free Coke Diet".

I hope I explained it well enough....

The worksheet kind of helps..

Thanks in advance.

You could use a User Defined Function for this.

Below is adapted from a solution to another post but based on your examples it should work ok... (apes your expected results)

Function KeywordFound(rngK As Range, rngR As Range, rngC As Range, _
                Optional boolPartial As Boolean = True, _
                Optional boolCaseSensitive As Boolean = False, _
                Optional strDelim As String = " ") As Variant
    Dim vK, vKW, vR, lngR As Long, lngW As Long, vbComp As VbCompareMethod, bMatch As Byte
    vbComp = IIf(boolCaseSensitive, vbBinaryCompare, vbTextCompare)
    vK = rngK
    vR = rngR
    'iterate keywords in reverse (assume longer keywords listed last - eg Apple, Apples etc...)
    For lngR = UBound(vK, 1) To LBound(vK, 1) Step -1
        bMatch = 1
        If boolPartial Then
            'words within keyword phrase can appear in any order
            vKW = Split(vK(lngR, 1), strDelim)
            For lngW = LBound(vKW) To UBound(vKW) Step 1
                If InStr(1, strDelim & rngC & strDelim, strDelim & vKW(lngW) & strDelim, vbComp) = 0 Then
                    bMatch = 0
                    Exit For
                End If
            Next lngW
        Else
            'look for keyword as phrase
            bMatch = -(InStr(1, strDelim & rngC & strDelim, strDelim & vK(lngR, 1) & strDelim, vbComp) > 0)
        End If
        If bMatch = 1 Then
            Exit For
        End If
    Next lngR
    KeywordFound = vR(lngR, 1)
End Function

The above, stored in a standard module in VBE, is invoked from a cell along the lines of other normal formulae - eg:

F18:
=KeywordFound($L$18:$L$22,$K$18:$K$22,$G18)
copied down

note: the code assumes (per sample) that codes are listed in order of length - ie Diet Pepsi after Pepsi, Diet Coke Free after Diet Coke and Diet Coke after Coke etc