FUZZY MATCHING AND LOOKUP FUNCTIONS FOR GOOGLE SHEETS
WHAT IS FLOOKUP?
Flookup is an advanced tool for performing fuzzy match and fuzzy lookup tasks in Google Sheets. It is the ideal solution for handling duplicate CRM data, analysing web crawl data and data-driven Search Engine Optimisation e.g. managing website migration redirects. With Flookup you can:
- Partial match strings.
- Remove duplicates.
- Highlight duplicates.
- Calculate the percentage similarity between strings.
- Extract unique values based on values from any column.
Unlike other lookup functions like XLOOKUP, VLOOKUP and INDEX/MATCH, Flookup functions are not affected by factors like the sort order of columns or rows, the presence of punctuation marks or the letter case of the text.
TEN REASONS WHY YOU SHOULD CHOOSE FLOOKUP
- It has familiar, intuitive and easy-to-use functions.
- It is secure. Flookup is a verified add-on that does not expose, store or share any of the spreadsheet data it processes, meaning that your data remains private and for your eyes only.
- It is fast. Flookup runs on one of the fastest fuzzy matching algorithms in the world and all its functions are optimised to complete tasks in the shortest time possible.
- It is robust. Flookup is powered by a battle-tested algorithm with millions of rows and columns of data under its belt.
- It is completely flexible. You can search any column with FLOOKUP and search any row with HFLOOKUP, not just the leftmost column or topmost row.
- It is dynamic. If the first match does not suit your needs, you can instruct Flookup to return the next best match until all possible matches have been exhausted.
- It has range. You can combine any number of strings as the lookupValue, giving you options to increase the specificity of your query.
- It speaks your language. Flookup is not limited to English; it can process text in most international languages.
- It offers value for money. Flookup is free forever, with select premium features available at very affordable fees.
- It is on the G Suite platform. Working out of the G Suite platform is full of benefits for individuals and organisations alike. Using Flookup taps directly into those benefits.
GETTING STARTED WITH FLOOKUP
- Install Flookup from the G Suite Marketplace.
- Activate premium features by following these steps:
- Open the spreadsheet in which you installed Flookup
- Go to Add-ons > Flookup > Manage subscription
- Select Monthly, Annual or Lifetime
- Enter the email address for which you want to activate Flookup.
- Click Activate
- Visit the tutorial page to learn how to use Flookup.
- Visit the changelog page to keep up-to-date with important changes to Flookup.
Flookup is available for free, with the option for users to subscribe for premium features. Please visit our pricing page for more details.
POPULAR FUZZY MATCHING ALGORITHMS
Fuzzy matching (also called partial or approximate string matching) is a technique for comparing strings that might have a less than 100% match. There are different techniques that are applied by fuzzy matching algorithms and the most popular involve the use of wildcard characters, word or phrase comparisons, regular expressions and edit distance. Examples include:
- Levenshtein Distance: This algorithm calculates and returns the minimum number of single-character edits (i.e. insertions, deletions or substitutions) required to change one word into another.
- Damerau–Levenshtein Distance: This algorithm is exactly like Levenshtein distance with one exception; it includes transpositions amongst its edits.
- Jaro–Winkler Distance: The Jaro distance between two words is the minimum number of single-character transpositions required to change one word into the other.
- n-gram: This is a contiguous sequence of n items from a given sequence of text or speech. The items can be phonemes, syllables, letters, words or base pairs according to the application.
- Soundex: This is a phonetic algorithm for indexing names by sound, as pronounced in English. The goal is for homophones to be encoded to the same representation so that they can be matched despite minor differences in spelling.
- Your Brain: "Aoccdrnig to a rscheearch at Cmabrigde Uinervtisy, it deosn't mttaer in waht oredr the ltteers in a wrod are, the olny iprmoatnt tihng is taht the frist and lsat ltteers be at the rghit pclae. The rset can be a toatl mses and you can sitll raed it wouthit porbelm. Tihs is bcuseae the huamn mnid deos not raed ervey lteter by istlef, but the wrod as a wlohe."
Flookup uses modified versions of n-gram and refined Soundex to analyse strings, depending on the function being used.