Fuzzy VLookUps

As you probably know, there’s a very useful Excel formula called VLOOKUP() which allows you to reference lookup tables and return the appropriate value.

On an Excel user support forum, I had previously linked to some formulas that provided “fuzzy” vlookups. In the event a perfect vlookup match couldn’t be found, the custom formula FUZZYVLOOKUP() returns the closest match available. The post has since 404’d, so I was asked to rehost them.

Click here to download a sample workbook.

If you open up developer mode, you’ll see a code module containing two custom functions: LIKENESS() and FUZZYVLOOKUP(). The former compares two text strings and returns a numerical measure of how similar they are (1 is perfectly similar, 0 is completely dissimilar). The latter iterates the likeness formula across a lookup table and returns the appropriate column of the best match. Both of these functions are demo’d below:

fl-fig1

Please note that FUZZYVLOOKUP() uses four arguments:

  1. The lookup value.
  2. The lookup table.
  3. The column from the lookup table you’d like to return.
  4. The minimum acceptable likeness. (In this example, I used 0.2).