How to Match and Merge Data in Google Sheets: An Flookup Tutorial

Manually merging or matching data from multiple sources can be a tedious and time-consuming task, especially when dealing with real-world data. Fortunately, Flookup is designed to streamline and simplify this process, making it easier to work with your messy data.

Flookup is a powerful data cleaning suite for Google Sheets. It has two fuzzy matching algorithms for cleaning your datasets regardless of spelling or any other text-based differences.

By streamlining the process of merging and matching data, Flookup can significantly improve the efficiency of data cleaning tasks.

Setting Up Your Data

We have two datasets of interest as shown in the image below:

• “Table 1”, which is our primary table, contains one column while “Table 2” contains three columns.
• “Table 2” contains the key values that we shall use as pointers to merge data to “Table 1”.
• The two empty columns E and F allow the function to return the matched value and its respective percentage similarity. Failure to include them might cause data to be overwritten.

Head to “Extensions > Flookup Data Wrangler > Match and Merge Functions > FLOOKUP” in your spreadsheet menu to launch and configure the sidebar by following these steps:

1. In the dropdown menu, select Return best match plus similarity.
2. Select “Primary range” data in range A3:A11 and click “Grab selected range”. This must be a single column.
3. Select “Secondary range” data in range D3:F12 and click “Grab selected range”.
4. Set the “Lookup_column” value to 1 because we are comparing A3:A11 to the first column of “Table 2”.
5. Set the “Return_column” value to 2 because we are returning data from the second column of “Table 2”.
6. Leave the “Threshold” value set to 0.6 in order to match fuzzy data.
7. Click Cell B3 as this is the position that we would like to start populating the results from.

Your results will present as shown above, with the results you requested [Column E] and the respective percentage similarities got by comparing the lookup values [Column A] and the match key values [Column D].

Our results are good but, because the default similarity is set to 0.6, only values that are exact or almost exact were matched.

In order to get more matches, we need to gradually lower the “Threshold” argument. In this case, we will reduce the “Threshold” value to 0.4 in our formula without adjusting the rest of the setup.

Our final table will end up as shown below:

Merging Full Rows of Data

To begin, setup your data to look something like the image below. We have inserted 3 new columns [B, C and D] because we want to merge data from the 3 columns of “Table 2”:

To merge data, head to “Extensions > Flookup Data Wrangler > Match and Merge Functions > FLOOKUP” in your spreadsheet menu and then configure the sidebar by following these steps:

1. In the dropdown menu, select Merge values from rows with matches.
2. Select “Primary range” data in range A3:A11 and click “Grab selected range”. This must be a single column.
3. Select “Secondary range” data in range E3:G12 and click “Grab selected range”.
4. Leave “Lookup_column” and “Return_column” value set to 1 because we are comparing A3:A11 to the first column of “Table 2” and also returning data from the first column of “Table 2”.
5. Lower “Threshold” to 0.4 having predetermined, in the previous section above, that this is the level of similarity that we need for this particular dataset.

Finally, to merge the two datasets, we do the following:

1. Click Cell B3 as this is where we want the merged data to start populating from.
2. Click “Get fuzzy matches”.

Your merged data will present as shown above. Always remember to leave enough empty columns to populate all the columns you have indexed.

You Might Also Like

• Match and Merge Data by Text Similarity