Fuzzy matching lookup in Excel

Question

I'm writing a spreadsheet for my finances and need a little help. I have a sheet of transactions and want to be able to automate the account that each transaction is assigned to via a set of rules on another sheet.

My transactions for example look like this: 1/04/16 -5.9 TEA'S ME acc_num 1/04/16 -8.5 CAFE 101 acc_num 1/04/16 -4.8 HOT WOK acc_num

I want to be able to have a set of rules like this in a lookup table: Backblaze 275 Countdown 300 Dropbox 275 Hot 300 Z 387 Pizzahut 300 Graham 184

Where if my transaction name contains something from the lookup table, the account number is looked up and placed in the cell where acc_num currently is.

I've tried using a vlookup function like so, but to no avail.

=IF(C2="", "", VLOOKUP("*"&C20&"*",'Chart Rules'!$A$2:$C$1001,2,0))

I realise that the above function does a fuzzy match on the lookup value, not the table that it's looking for the values in.

I'd appreciate any suggestions in how to do this! Cheers

Answer 1

Here is a VBA solution which implements a rather weak fuzzy lookup. Maybe it will work on a Mac, maybe not (I've had mixed experiences when a colleague who has a Mac has tried to run some of my programs):

Function FLOOKUP(pat As String, arr As Variant, ColNum As Long, Optional CaseSensitive = True) As Variant
    'does a linear search of first column of array or range arr until it finds a
    'string which is a fuzzy match for pat, returning the corresponding
    'entry in column ColNum of arr. If no match is found NA is returned

    Dim A As Variant, i As Long, s As String, p As String, pStar As String

    p = IIf(CaseSensitive, pat, UCase(pat))
    pStar = "*" & p & "*"

    If TypeName(arr) = "Range" Then
        A = arr.Value
    Else
        A = arr
    End If

    For i = LBound(A) To UBound(A)
        s = A(i, 1)
        If Not CaseSensitive Then s = UCase(s)
        If p Like "*" & s & "*" Or s Like pStar Then
            FLOOKUP = A(i, ColNum)
            Exit Function
        End If
    Next i

    FLOOKUP = CVErr(xlErrNA)
End Function

In this code s is considered to be a fuzzy match of t if and only if either s Like "*" & t & "*" or t Like "*" & s & "*". In other words, if either s is a substring of t or vice-versa.

If Excel VBA for the Mac doesn't know about xlErrNA, experimentation suggests that this is just 2042. You could always return e.g. False rather than an error code if no match is found.

The linear nature of the search implies that it probably won't scale very well, but perhaps it will work for your application.