This article covers a Google Sheets addon for performing fuzzy match, removing duplicates, etc. Flookup is a Google Sheets addon which can perform the above actions on your data. Flookup is a verified addon for Google Sheets which is simple to use. The best feature of this addon is that it is not affected by typos in the data. It will search and match data regardless of mistakes or typing errors.
You can perform the following functions using the Flookup addon:
- Compare text.
- Highlight duplicates.
- Remove duplicates.
- Get unique values from a column.
- Merge cells with partial matches.
- Fuzzy match
- Take out the percentage similarity between strings.
- Based on partial matches, find sum or average of numbers.
This addon also has a paid premium version. You can access all these features using the paid version which is available with monthly, yearly, or for lifetime by making one payment. There is a free plan as well which is a little limited in its functions. Like the free plan processes only 50 rows at a time, it does offer unlimited use of some functions, and you do not require a credit card for this free plan. Some functions like highlighting duplicates, compare text, etc. are not available in the free version.
How to install the Flookup addon in Google Sheets:
To install this addon in Google Sheets, click this link. It will open a page like the one you see in the screenshot above. This is the G Suite Marketplace page. You see the Flookup addon displayed on the page with a install button given on the right side of the page. Click the install button to add this plugin to your Google sheets.
A pop up window will come up asking you to give access to your Google account to install this addon. Go ahead and allow this. Your addon has been installed and it can now be accessed from the add-ons menu of your Google Sheets. So when you open any Google sheet and go to your add-ons menu you will see Flookup added to it, as can be seen in the screenshot below.
To activate premium features, on your spreadsheet go to addons menu and select Flookup. From the menu which comes up select manage subscription. Then choose your plan from the drop down menu and enter your email address in the provided box. The subscription feature is for users who have paid for a subscription to this addon. For people using the basic free plan, these features shown on the add-ons menu are not available to you.
But you can use the some commands which are available in the free version. To better understand how this addon works you can go through the tutorial and documentation given about it on the website or click the given links. You can also read about the popular fuzzy matching algorithms being used in this addon.
How to Fuzzy Match in Google Sheets?
Let’s see how to fuzzy match in Google sheets. For example, you have a sheet with data as shown in the above screenshot. You have name in the first column, salary in the second column, and the third column contains just names.
Your objective is to match the names of column 3 from column 1 and pull out the salaries of each person and update the correct salaries for names in column 3. So now you need to write the formula for it, which should be:
=FLOOKUP(lookupValue, tableArray, lookupCol, indexNum, threshold, rank)
So for the above Google Sheet this will translate into:
=FLOOKUP(C2,$A$2:$B$16,1,2,0.1)
This function will match the names in column 3 with that in column 1 and then match the salaries given. So even if the list of names in column 3 is random and has some typos, the salaries would be matched with some errors as can be seen in the screenshot below.
So for the names it was not able to match the salary, we will run this query again with a bit of change in the function. Everything remains same but at the end we will add 0.5 percent similarity level. This 0.5 increases the similarity precision and gives better results. So now your query looks like the one below.
=FLOOKUP(C3,$A$2:$B$16,1,2,0.5)
Now the results which had errors earlier would be fixed and the new results are displayed in the screenshot below.
Now the data is matched perfectly. In case you get error again you can change the percent similarity level further and run the query again.
How to Remove Duplicates with ULIST?
When you need to find unique data from the list and remove duplicate copies of the same data, then use the ULIST function in Google sheets. Using this function you can remove duplicate data from a sheet and get unique values. So we will try this on the same table as we used in the fuzzy match example.
In this list there are several names which have been repeated. Now we want only data with unique values with out the duplicates. So we run the ULIST query like seen below:
=ULIST(colArray, indexNum, threshold)
For the table we are using this will translate to:
=ULIST(A2:B20,1)
This query will run and pull out unique data from the given table, as can be seen in the screenshot below. The heading “unique by name” column contains unique data picked out from the “full name” table in column A.
Again if there are some errors, then you can change the similarity percentage level in the query and run again to get error free results.
How to get Sound match results in Google sheets?
Apart from fuzzy match and remove duplicate data this addon let’s carry out another function called soundmatch syntax, where similar sounding data is pulled even if there are mistakes or typos.
Like in the table we are using we can pick out a name and ask the soundmatch function to pull out names which sound similar to the name we picked. This function will pull out all the name which sound similar to the one we picked even if there are typos or mistakes in the other names.
The syntax for soundmatch is given below:
=SOUNDMATCH(lookupValue, tableArray, lookupCol, indexNum, rank)
So for the table we are using, this will translate into the below given syntax:
=SOUNDMATCH(A11,A2:C20,3,3,1)
So we picked the name in the “A11” cell and we want the function to search any other name which sounds similar to this one. If yes, then all the names which sound similar will be shown in the result.
As you can see in the above screenshot, we picked out the name in “A11” which is highlighted in red. The syntax searched for a name sounding the same and pulled out the result in the soundmatch column on the right. Even with typos the soundmatch syntax was able to find a similar name.
Overview:
Flookup addon offers some very good functions with its free version like fuzzy match, remove duplicates, soundmatch, etc. The functions provide near accurate results and are easy to understand. This addon is optimized to run the fuzzy matching algorithm in the shortest time possible. Thus, making this addon fast and reliable. If you find the functions provided great and need more functions for your Google sheets, then you can upgrade to the paid version of the addon which contains more functions in it. Try this addon for Google Sheets.
