MERGING DATA IN GOOGLE SHEETS USING FLOOKUP

Brief Introduction

Everyone needs to lookup or merge data from different sources at least once 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, and please note that the images we have used are for illustration purposes only.

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 to which we aim to add data.

• "Table 2" is the one whose columns contain the key values that we shall use to merge its data to "Table 1".

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 copy 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:

• Index values from Column A ["Table 1"] > A3:A11

• Index all the values from Column D to Column F ["Table 2"] > D3:F12

• Search through the first column of "Table 2" to identify rows with matches > 1

• Return values from the second column of "Table 2" that correspond to the rows with matches > 2

Your results will present as shown below, showing the results you requested to be returned [Column E] paired with the respective percentage similarities between 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, expressed like this:

=FLOOKUP(A3:A11,C3:c11,1,2,0.4)

Our final table will end up as shown below:

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

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

• Running over much larger datasets using the Long Run Mode (LRM) feature.

• Returning values from any column to the left or right of the column containing match key values.

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:

• Index values from Column A > A3:A11

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

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

• Return rows where matches defined by a predetermined minimum level of similarity, have been found > 0.4

Your merged data will present 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 cell 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 most of the 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 allows that 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 #1" / "Lookup_column" and "Index #2" / "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.

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

You Might Also Like

• How to Lookup and Merge Data