Flookup: fuzzy lookup function

The flookup excel add-in allows to check if a given string, hereunder called the description, contains at least one substring out of a list of supplied substrings, hereunder called the database. The flookup function (fuzzy lookup) allows for limited spelling errors and typo’s and allows for permutations of words within the substring making it ideally suited to extract names or keywords from manually entered descriptions in logfiles and financial data.

For instance, imagine that out of the description “Do not forget to repay mr. Olaf Johnson”  in a todo list of a restaurant owner we wish to extract the name of the client that should be repaid. Flookup will be called with 2 parameters

• The description
• An excel range containing the names of all potentiel clients

Flookup will return the closest matching client, and accept any of the following “Olaf Johnson”, “Johnson, Olaf” or “Olaf Johanson”. Matching is based on the Levenshtein distance metric.

Syntax

flookup(description As String, database as Range, Optional max_distance As Integer = 3)

• description: a long description containing a substring that we are looking for
• database: link to all acceptable substrings. The flookup function will return the closest one
• max_distance: maximal Levenshtein distance allowed. The Levenshtein distance measure amounts to one character

Download

An example and the flookup add-in can be downloaded here: flookup_Addin_001

See installation guide below for further instructions on installing the add-in and running the example sheet.

Installation guide

• Within Excel, Choose file -> options. Then select Add-ins
• At the bottom of the form, choose “Excel Add-ins” and press “Go”

• Browse for the xlam file “Flookup_Addin_001.xlam” to enable the add-in. The add-in will appear as “Flookup_Addin_001”. In order to ensure the add-in is easily found, it might be wise to place it in the standard location: C:\Users\<Username>\AppData\Roaming\Microsoft\AddIns

• Restart excel and launch the example sheet provided in the ZIP