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.
If you open up developer mode, you’ll see a code module containing two custom functions:
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:
Please note that
FUZZYVLOOKUP() uses four arguments:
- The lookup value.
- The lookup table.
- The column from the lookup table you’d like to return.
- The minimum acceptable likeness. (In this example, I used 0.2).