How to Fuzzy Lookup in Google Sheets
Summary of steps:
=FLOOKUP(lookupValue, tableArray, lookupCol, indexNum, threshold, [rank])
- Type a range or type a value as the lookupValue.
- Select the tableArray.
- Enter the lookupCol which is the column in the tableArray to analyse.
- Enter indexNum, which is the column in tableArray from which results should be returned.
- Press Return or Enter.
One of the most popular functions of Flookup is the eponymous FLOOKUP and it can be used to find fuzzy matches or merge data in Google Sheets.
In this example, we have two tables. One contains a list of individuals and their respective monthly salaries, and another only contains list of individuals:
We can use FLOOKUP to match names in the list titled "Work List" to the names in the list titled "Full Name", in order to return their respective salaries and therefore merge the data. By writing the formula like this,
=FLOOKUP(D2,$A$2:$B$15,1,2), with the default percentage similarity of 0.6, we get the following results:
As you can see, even at a 0.6 level of similarity, some matches are not caught. In order to fix this, we simply keep adjusting the threshold parameter downwards until all possible matches are found.
The initial parameter, the lookupValue, can either be a single string, concatenated strings or an array of strings. For example:
- We can write the FLOOKUP formula like this:
=FLOOKUP(D2&F2,$A$2:$B$15,1,2). This formula combines the values in D2 and F2 into one string. This is especially useful for distinguishing similar looking strings that are not exactly duplicates by analysing extra data.
- We can also write the FLOOKUP formula like this:
=FLOOKUP($D$2:$D$15,$A$2:$B$15,1,2)for double the speed and efficiency. This also prevents the spreadsheet from hanging during long operations.
- All results are returned with their respective percentage similarities.