TUTORIAL

In this tutorial we are going to look at a few examples of how to use Flookup to match or lookup fuzzy matches right inside Google Sheets. Flookup runs on a robust engine that powers through any text you might throw at it, making it the perfect solution for your data quality needs.

HOW TO FUZZY MATCH IN GOOGLE SHEETS WITH FLOOKUP

One of the most popular functions of Flookup is the eponymous FLOOKUP. You can use it to lookup tables and find matches or merge data. In the illustration below there is a list of names of individuals and their respective monthly salaries:

Fuzzy match with Google Sheets

When presented with a separate list of names and you would like to get the right salary of every individual by searching a database, you can use the FLOOKUP formula as illustrated below:

Fuzzy match with Google Sheets

We used this function =FLOOKUP(D2,$A$2:$B$15,1,2), with a default percentage similarity of 0.6 and got these results:

Fuzzy match with Google Sheets

As you can see, even at a 0.6 level of percentage similarity, some results were not caught. In order to fix this, we simply adjust the threshold parameter downwards until we get all possible results. Highlighted in yellow are the results at a 0.5 percentage similarity level:

Fuzzy match with Google Sheets

And these are the results at a 0.3 percentage similarity level, at which point all possible matches have been found:

Fuzzy match with Google Sheets

And that concludes our brief tutorial on how to fuzzy match with Flookup. These same principles demonstrated here also hold true for HFLOOKUP function.

HOW TO REMOVE DUPLICATES IN GOOGLE SHEETS WITH ULIST

Duplicates in our data is a constant headache for every single professional out there. This post by HubSpot highlights the enormous costs and list of issues that organisations face because of not dealing with duplicates and poor data quality in general. Luckily, with Flookup, remove duplicates for your data e.g. contacts, leads, etc, is a simple and intuitive process.

Suppose you have a list of individual names, the cities they live in and their respective salaries:

Remove duplicates with Google Sheets

This list obviously contains duplicates under the second column showing cities. In order to extract a list of individuals without the duplicated cities, you can use the ULIST formula as shown below:

Remove duplicates with Google Sheets

And the results will look like this:

Remove duplicates with Google Sheets

But, as we can see, there was a duplicate that was not caught because it contained a typo. That duplicate was "New Yorke" vs "New York". In order to resolve this, we need to adjust the threshold parameter:

Remove duplicates with Google Sheets

And finally we have a clean list with no duplicates:

Remove duplicates with Google Sheets

If you are a fan of Google's UNIQUE function, you will sure love ULIST and the power it brings to your Google Sheet.

HOW TO HIGHLIGHT DUPLICATES IN GOOGLE SHEETS

Highlighting duplicates is one of the best ways to get an overview of the state of your data. Unfortunately, most of the data we tend to work with contains partial matches, punctuation marks or are that variables which significantly throw off traditional methods you might know. However, with Flookup, the story is very different.

Highlight duplicates with Google Sheets

We can see that, in the illustration above, that the column showing cities has potential duplicates; we want to highlight the entire data set based on this column. We go about this by selecting our range of interest and then heading to Add-ons > Highlight duplicates > By percentage:

Highlight duplicates with Google Sheets

In the next window we make necessary adjustments, specifically the column we want to analyse for duplicates and the minimum level of percentage similarity that we desire:

Highlight duplicates with Google Sheets

After running the function, duplicates are highlighted in yellow:

Highlight duplicates with Google Sheets

But wait, there is more! We can also highlight duplicates by similarity in sound. To do this, we follow the same steps as above but this time we click Add-ons > Highlight duplicates > By sound:

Highlight duplicates with Google Sheets

A window pops up prompting you to enter the column to analyse and then duplicates are highlighted in aqua after running the function:

Highlight duplicates with Google Sheets
Highlight duplicates with Google Sheets

That concludes our highlight tutorial. Please note that the same rules demonstrated here also apply for deleting duplicates with Flookup.