FUZZY MATCHING AND LOOKUP FUNCTIONS FOR GOOGLE SHEETS
Flookup is an advanced tool for performing fuzzy match and fuzzy lookup tasks in Google Sheets. With Flookup, you can perform the following actions regardless of variations in spelling or the existence of typos and partial matches in your data:
- Find and match strings.
- Remove duplicates.
- Highlight duplicates.
- Calculate the percentage similarity between strings.
- Find the sum or average of numbers that correspond to partial matches.
- Merge cells that contain partial matches from a single column.
- Extract unique values from any column.
Additionally, Flookup functions are not limited by factors like the sort order of columns or rows, the presence of punctuation marks or the letter case of the text, thereby saving you from the time and hard work it takes to clean or normalise your data.
10 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 reliable. 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, with a perfectly priced premium experience available when you need it.
- 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 Annual or Lifetime
- Enter the email address for which you want to activate Flookup.
- Click Activate
- Click on Add-ons > Flookup > Sync Flookup. You only have to do this once.
- Visit the documentation page to view details about Flookup functions.
- Visit the changelog page to keep up-to-date with important changes to Flookup.
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 (in combination with functions like VLOOKUP or INDEX/MATCH), word or phrase comparisons, regular expressions and edit distance. Examples of such implementations 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.
- However Your Brain Works: "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."