Provides functions FUZZY.LOOKUP and FUZZY.MATCH which are similar to the native Excel functions XLOOKUP and XMATCH, but are able to perform approximate text matching (aka fuzzy matching) of the lookup_value to the lookup_array or range. Algorithms run in Excel so your data never leaves your spreadsheet, and you have unlimited free use.

FEATURES

🎯Similar to XLOOKUP and XMATCH, easy to use.

🆓Unlimited free use, no need for a subscription.

🔒No data is shared outside your spreadsheet.

🚀Fast algorithm handles large datasets.

✅Outputs lookup and match for quick comparison.

FUNCTIONS

FUZZY.MATCH (like XMATCH, with fuzzy matching)

➡️Inputs: lookup_value, lookup_array, [threshold]

⬅️Outputs: Lookup, Fuzzy Match, Similarity, and Row as custom data type

🔍Searches for the lookup_value in the lookup_array using fuzzy text matching.

🎚️Threshold input lets you adjust how close the match must be.

📅Output is match row in lookup_array, use with INDEX(), etc.

FUZZY.LOOKUP (like XLOOKUP, with fuzzy matching)

➡️Inputs: lookup_value, lookup_array, return_array, [threshold]

⬅️Outputs: Lookup, Fuzzy Match, Similarity, and values from matching row in return_array

🔍Searches for the lookup_value in the lookup_array using fuzzy text matching.

🎚️Threshold input lets you adjust how close the match must be.

📅Output return_array retrieves selected columns from the matching row.

APPLICATIONS

Find and remove duplicates: Using FUZZY.MATCH with the same column as input for both the lookup_value and lookup_array, the output will be the fuzzy duplicates, and the rest of the rows will be blank. You can then easily delete the duplicate row(s) from your table.

Match leads with your CRM: If you have marketing leads and want to match them with companies already in your CRM, you can use FUZZY.LOOKUP where the lookup_value is the name of the company from the lead, and the lookup_array is the column of all of the companies in your CRM. Setting the return_array to the CustomerID column in the CRM table will return the CustomerID for the matches to enable easy import to your CRM. You can use a similar process to match the names of the leads with contacts in the CRM, since people don't use their business email address when filling out lead forms as that would just make the matching too easy.😉

Find applicants in HR system: Let's say you have a list of job applicants and you want to see which ones are already in your HR system. Using FUZZY.LOOKUP, the lookup_value is the name of the applicant, and the lookup_array is all of the previous applicants from the HR system. The return_array is the additional columns from the HR system you want retuned with a match (e.g. date of last application, last position applied for, etc.).

Master Data Management (MDM): Fuzzy matching can be a powerful tool in MDM to identify and link related records across different systems or datasets. This can help in deduplicating, entity resolution, linking, cleansing, and standardizing data, thereby improving the overall quality of your master data.

LIMITATIONS

The main limitation of the current version is that the algorithm works better looking for substrings in lookup_array that match the lookup_value, than the reverse. For example, a lookup_value of "Microsoft" matches a lookup_array element of "Microsoft Corporation" with an almost perfect match similarity of 0.99, because "Microsoft" is a substring of "Microsoft Corporation". However, when done in reverse, a lookup_value of "Microsoft Corporation" will only match a lookup_array element of "Microsoft" with 0.43 similarity. So where possible, try to use the shorter string as the lookup_value. One way to achieve this is use Excel functions to remove words that are not needed for the matching (e.g. Corporation, Inc., etc.) from the lookup_value.

Also, remember that the accuracy of fuzzy matching, like most things in machine learning, might be awesome in one situation and useless in another.🙂

See Fuzzy Match for Excel on our website for more information.

Fuzzy Match for Excel

by Boardflare