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:
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:
We used this function
=FLOOKUP(D2,$A$2:$B$15,1,2), with a default percentage similarity of 0.6 and got these results:
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:
And these are the results at a 0.3 percentage similarity level, at which point all possible matches have been found:
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:
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:
And the results will look like this:
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:
And finally we have a clean list with no duplicates:
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.
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:
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:
After running the function, duplicates are highlighted in yellow:
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:
A window pops up prompting you to enter the column to analyse and then duplicates are highlighted in aqua after running the function:
That concludes our highlight tutorial. Please note that the same rules demonstrated here also apply for deleting duplicates with Flookup.