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.

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.

A section of a spreadsheet showing six columns. Columns D, E and F have data, while columns B and C only have titles.

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:

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

A section of a spreadsheet showing six columns. Columns B and C have text entries and numbers paired respectively. Two cells show a "!NoMatchFound" error.

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:


Our final table will end up as shown below:

A section of a spreadsheet showing six columns, fullfilling the goal of this post.

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:

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:

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

A section of a spreadsheet showing merged data

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

A section of a spreadsheet showing how to setup tables before merging

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:

A representation of the default sidebar configuration for merging data with Flookup

To configure the sidebar, follow these steps:

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

A representation of the Flookup sidebar function for merging data, with custom parameters

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

You Might Also Like