MERGING DATA IN GOOGLE SHEETS USING FLOOKUP

Brief Introduction

Everyone needs to lookup or merge data from different sources at least one in their lifetime and doing this manually is simply not sustainable in real-world situations.

Let us look at a simple example on how to lookup and merge data using Flookup.

Setting Up Your Data

We have two datasets; one with one column and a second dataset with three columns as shown in the image below.

• "Table 1" will be our lookup table. This is the table we aim to add data to.

• "Table 2" is the one whose columns we shall search for key values with the aim of merging its data to "Table 1". It is implied that this table contains trustworthy data.

We have also inserted two empty columns between the tables. These will take the matched value and its respective percentage similarity. If you do not follow this setup, Google Sheets will return a #REF error.

Type this formula in Cell B2: =FLOOKUP(A3:A11,D3:F12,1,2) and press ENTER/RETURN.

This is what happens as we step through the formula:

• Pick values from Column A > A3:A11

• Index all the values from Column D to Column F > D3:F12

• Search through Column D [the first column of "Table 2"] for matches > 1

• Return values from Column E [the second column of "Table 2"] from the row with the match > 2

Your results will look like this, showing the result you requested and the respective percentage similarity between the lookup value and the match key values from "Table 2":

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" parameter. In this case, we will reduce the "threshold" value to 0.4 in our formula, expressed like this:

=FLOOKUP(A3:A11,C3:c11,1,2.0.4)

Our final table will end up looking as shown below:

In this example, we have explored a very small part of FLOOKUP parameters. For example, other tasks we have not explored that FLOOKUP can be used to do are:

• Return the next best result in case the default match is not to your liking.

• Run over much larger datasets using the Long Rum Mode feature.

• Lookup any column to the left or right of the column containing match key values in "Table 2".

Merging Full Rows

Flookup can also be used to merge entire rows and, therefore, combine two tables together.

Taking the dataset we have above as an example, let us attempt to merge "Table 2" with "Table 1".

To do this, type this formula in Cell B2 and press ENTER/RETURN:

=MLOOKUP(A3:A11,E3:G12,1,0.4)

This is what happens as we step through the formula:

• Pick values from Column A > A3:A11

• Index all the values from Column E to Column G > E3:G12

• Search through Column E [the first column of "Table 2"] for key values > 1

• Return rows where matches with a predetermined minimum similarity, have been found > 0.4

Your merged data will appear as shown below. Always remember to leave enough room to populate all the columns you have indexed:

Merging Full Rows in Large Datasets

So far, we have been using standard functions to merge data but, unfortunately, these kinds of functions are only allowed to run for 30 seconds in Google Sheets. This means, if you try to merge large datasets, the function will timeout before processing much data.

To overcome this hurdle, Flookup allows you to run some functions, like merge, using a feature we call the Long Run Mode (LRM). This feature empowers the function to run for a total of 6 minutes.

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 using the LRM, head to Extensions > Flookup > Long Run Mode (LRM) > FLOOKUP in your spreadsheet menu. A side bar that looks like this, will open:

To configure the sidebar, follow 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 "Index One" and "Index Two" 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.

Your setup should look something like this after following these steps:

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".

To find out more about the full range of functions for merging data with Flookup and how to use them to merge your data, please read our comprehensive documentation on the same.