Lookup customer type by the meaningful part of the customer name and set prioritize

Question

1. Is there any way excel 2010 can lookup customer type by using meaningful part of customer name? Example, The customer name is Littleton's Valley Market, but the list I am trying to look up the customer type the customer names are formatted little different such as <Littletons Valley MKT #2807 or/and Littleton Valley.

2. Some customer can be listed under multiple customer types, how can excel tell me what which customer and can I set excel to pull primary or secondary type?

Answer 1

Re #1. Fails on the leading < (if belongs!) and any other extraneous prefix but this may be rare or non-existent so:

=INDEX(G:G,MATCH(LEFT(A1,6)&"*",F:F,0))  

or similar may catch enough to be useful. This looks at the first six characters but can be adjusted to suit, though unfortunately only once at a time. Assumes the mismatches are in ColumnA (eg A1 for the formula above) and that the correct names are in ColumnF with the required type in the corresponding row of ColumnG.

On a large scale Fuzzy Lookup may be helpful.

Since with a VBA tag Soundex matching and Levenshtein distance may be of interest.

Re #2 If secondary type is in ColumnH, again in matching row, then adjust G:G above to H:H.

Answer 2

pnuts gives a good answer re: Fuzzy Lookup, Soundex matching, etc. Quick and dirty way I've handled this before:

Function isNameLike(nameSearch As String, nameMatch As String) As Boolean
    On Error GoTo ErrorHandler

    If InStr(1, invalidChars(nameSearch), invalidChars(nameMatch), vbTextCompare) > 0 Then isNameLike = True
    Exit Function

ErrorHandler:
    isNameLike = False
End Function
Function invalidChars(strIn As String) As String
    Dim i As Long
    Dim sIn As String
    Dim sOut As String

    sOut = ""
    On Error GoTo ErrorHandler

    For i = 1 To Len(strIn)
        sIn = Mid(strIn, i, 1)
        If InStr(1, " 1234567890~`!@#$%^&*()_-+={}|[]\:'<>?,./" & Chr(34), sIn, vbTextCompare) = 0 Then sOut = sOut & sIn
    Next i
    invalidChars = sOut
    Exit Function

ErrorHandler:
    invalidChars = strIn
End Function

Then I can call isNameLike from code, or use it as a formula in a worksheet. Note that you still have to supply the "significant" part of the customer name you're looking for.