FUZZY MATCHING IN EXCEL: SOLUTIONS FOR MAC USERS

ON THIS PAGE

The Challenge: No Fuzzy Lookup Add-in for Excel on Mac
The Built-in Option: Power Query's Fuzzy Merge on Mac
The DIY Approach: VBA and Complex Formulas
Why Traditional Fuzzy Matching Falls Short
The Better Alternative: Flookup for Google Sheets
Power Query Versus Flookup: A Quick Comparison
Best Practices for Any Method
Final Thoughts
You Might Also Like

THE CHALLENGE: NO FUZZY LOOKUP ADD-IN FOR EXCEL ON MAC

For years, Mac users have faced a significant gap in their Excel toolkit: the absence of the official Microsoft Fuzzy Lookup Add-in. This powerful tool, a staple for data cleaning on Windows, was never made available for macOS, leaving users to rely on cumbersome workarounds.

This gap forced Mac users into inefficient processes, such as:

Tedious manual data cleaning, which is prone to human error and time-consuming.
Exporting data to a Windows machine or a virtual machine to utilise the add-in.
Attempting to replicate fuzzy matching logic using complex, often unreliable, combinations of Excel formulas, which rarely achieve the same level of accuracy or automation.

THE BUILT-IN POWER QUERY FUZZY MERGE

Fortunately, the situation has improved. Microsoft has brought Power Query to Excel for Mac and with it comes a built-in "Fuzzy Merge" feature. This is a significant step forward, allowing users to perform approximate matches directly within Excel on macOS for the first time.

Power Query's Fuzzy Merge is a capable tool for basic fuzzy matching tasks. It allows you to merge tables based on similar text and is a huge improvement over having no native tools.

However, it relies on the same lexical algorithm (Jaccard index) as the old Windows add-in, which has its limitations. It is great for catching simple typos but struggles with more complex variations.

A DIY APPROACH WITH VBA AND FORMULAS

For those willing to get their hands dirty, it is possible to create a custom fuzzy matching solution using a combination of Excel formulas and Visual Basic for Applications (VBA). This method typically involves two key steps:

Creating a Custom Levenshtein Function: You can write a VBA script to create a custom function, for example, "LEVENSHTEIN()" that calculates the "edit distance" between two strings. This requires using the VBA editor.
Combining with Standard Formulas: Once you have your custom function, you can combine it with "INDEX", "MATCH" and other array formulas to find the row with the highest similarity score and return the corresponding value.

While this approach offers a high degree of control, it has significant drawbacks.

It requires coding knowledge and can be very slow on large datasets, making it impractical for most users as a day-to-day solution.

WHY TRADITIONAL FUZZY MATCHING FALLS SHORT

Traditional fuzzy matching, like that in Power Query, works by comparing the characters or words in two strings. It calculates a similarity score based on how many elements they share. For example, it can easily see that "John Smith" and "Jhon Smith" are very similar.

However, this approach fails when the meaning is the same but the words are different. Consider these examples where it would struggle:

"The big apple" versus "New York City"
"Chief Exec. Officer" versus "CEO"
"United States of America" versus "USA"

A traditional algorithm would see these pairs as completely different because they share few, if any, common words. This is where a more intelligent approach is needed.

A BETTER ALTERNATIVE WITH FLOOKUP

For those who need more power, accuracy and flexibility than Power Query can offer, Flookup Data Wrangler for Google Sheets is a far more advanced solution. Its cloud-based nature makes it perfectly accessible and incredibly powerful for Mac users.

Flookup moves beyond traditional algorithms by leveraging sophisticated AI-powered semantic matching. It does not just compare text; it understands meaning.

This allows it to intelligently identify connections that other tools miss, such as matching "Chief Executive Officer" with "CEO."

By using Flookup Data Wrangler, Mac users can:

Perform advanced fuzzy lookups: Merge disparate datasets, such as combining customer lists with slight name variations.
Identify and remove duplicate entries: Detect and eliminate redundant records, even with minor variations, preventing data integrity issues.
Clean and standardise messy data: Transform inconsistent entries like "New York", "NY" and "NYC" into a single, uniform format.
Automate data matching processes: Schedule recurring matching tasks to run automatically, saving significant time and effort.

COMPARING POWER QUERY AND FLOOKUP

Feature	Power Query (Excel)	Flookup (Google Sheets)
Core Technology	Compares text characters using the Jaccard similarity algorithm	Compares text by meaning using advanced AI models.
Best For	Simple typos and minor spelling variations.	Complex variations, synonyms and acronyms.
Collaboration	File-based, limited real-time options.	Cloud-native, built for real-time team collaboration.
Ease of Use	Integrated, but can have a steeper learning curve.	Simple and powerful custom functions.

BEST PRACTICES FOR ANY METHOD

Regardless of the tool you choose, following these best practices will ensure better and more reliable results:

Clean Your Data First: Before matching, use data cleaning tools to remove extra spaces, standardise case and remove irrelevant punctuation. This reduces unnecessary variations.
Test with a Small Sample: Before running a fuzzy match on your entire dataset, test it on a small, representative sample to ensure the settings and threshold are correct.
Always Back Up Your Data: Data cleaning can be a destructive process. Always work on a copy of your original data to prevent accidental data loss.

FINAL THOUGHTS

While Excel for Mac now has a basic fuzzy matching capability through Power Query, it is not the most powerful tool available.

For Mac users who need truly advanced, accurate and intelligent data matching, Flookup for Google Sheets is the definitive solution.

It overcomes the limitations of older, algorithm-based tools by bringing AI-powered semantic understanding to your data, all within a collaborative, cloud-based environment that works seamlessly on any operating system.