HOW TO LOOKUP AND MERGE DATA

Introduction to Lookup and Merge

Flookup has five lookup functions: FLOOKUP, QLOOKUP, MLOOKUP, HLOOKUP and SOUNDMATCH. The first four functions find and return matches based on their respective percentage similarities, while the last one returns matches based on the Soundex algorithm.

FLOOKUP

=FLOOKUP(lookupValue, tableArray, lookupCol, indexNum, [threshold], [rank], [range])

Use the FLOOKUP function to search any column of a range of cells, and then return a value from a specified column of that range.

FLOOKUP Parameters

    • lookupValue [Required]. The value to search for in any column of tableArray. The lookupValue argument can be a text entry, concatenated text entries, a single cell reference or a range of many rows.

    • tableArray [Required]. The range of cells that contains data. The values in the user-specified column of tableArray are systematically compared to lookupValue. These values can be text, numbers, or other values.

    • lookupCol [Required]. The column number in tableArray which will be searched by the function. A lookupCol value of 1 searches the first column in tableArray, a lookupCol value of 2 searches second column in tableArray, and so on. Values must be greater than 0.

    • indexNum [Required]. The column number in tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first column in tableArray, an indexNum of 2 returns the value in the second column in tableArray, and so on. Values must be greater than 0.

    • threshold [Optional]. A variable that specifies whether you want Flookup to find an exact match or an approximate match. It is the similarity level below which results WILL NOT be returned from tableArray. Values range from 0 [no filtering] to 1 [only exact matches returned]. The default value is 0.6.

    • rank [Optional]. A variable that specifies the rank of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to n (the nth possible match). The default value is 1 and results are ranked in order of descending percentage similarity.

Using the Long Run Mode

    1. Head to Extensions > Flookup > Long Run Mode > FLOOKUP in your spreadsheet menu.

    2. Select the mode you want this function to run. It can either be Return the best match plus similarity, Return the best match plus row number or Return all matches for each item.

    3. Primary range: Select range of one or more columns and click "Get selected range".

    4. Secondary range: Select a separate range of one or more columns and then click "Get selected range".

    5. Index One: Enter the index of the column of values in "Primary range" that you want to search for in "Secondary range".

    6. Index Two: Enter the index of the column of values in "Secondary range" that you want to be matched with values from "Primary range".

    7. Threshold: Enter the minimum percentage similarity below which values should not be returned.

    8. Click an empty cell where you want your results to be displayed.

    9. Click "Get fuzzy matches".

Key Points

    • FLOOKUP returns the lookup result and its respective percentage similarity or its respective position in tableArray. You, therefore, need to allow two columns for the results to be displayed. If you forget to do this, an error message will be displayed instead.

    • If FLOOKUP LRM times out, the results that have been processed up to that point, will be displayed.

MLOOKUP

=MLOOKUP(lookupValue, tableArray, lookupCol, [threshold], [rank])


This function is basically a subsection of FLOOKUP and so they both work exactly the same way, but with one key difference:

With MLOOKUP, the entire row of tableArray in which the match is located, will be returned and therefore this function can be very useful in merging different lists or tables.

When using MLOOKUP, please leave enough room for results to be displayed properly.

MLOOKUP Parameters

    • lookupValue [Required]. The value to search for in any column of tableArray. The lookupValue argument can be a text entry, concatenated text entries, a single cell reference or a range of many rows.

    • tableArray [Required]. The range of cells that contains the data. The values in the user-specified column of tableArray are systematically compared to lookupValue. These values can be text, numbers, or other values.

    • lookupCol [Required]. The column number in tableArray which will be searched by the function. A lookupCol value of 1 searches the first column in tableArray, a lookupCol value of 2 searches second column in tableArray, and so on. Values must be greater than 0.

    • threshold [Optional]. A variable that specifies whether you want Flookup to find an exact match or an approximate match. It is the similarity level below which results WILL NOT be returned from tableArray. Values range from 0 [no filtering] to 1 [only exact matches returned]. The default value is 0.6.

    • rank [Optional]. A variable that specifies the rank of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to n (the nth possible match). The default value is 1 and results are ranked in order of descending percentage similarity.

QLOOKUP

=QLOOKUP(lookupValue, tableArray, lookupCol, indexNum, [threshold])

Use the QLOOKUP function to search any column of a range of cells, and then return a value from a specified column of that range.

QLOOKUP Parameters

    • lookupValue [Required]. The value to search for in any column of tableArray. The lookupValue argument can be a text entry, concatenated text entries, a single cell reference or a range of many cells.

    • tableArray [Required]. The range of cells that contains the data. The values in the user-specified column of tableArray are systematically compared to lookupValue. These values can be text, numbers, or other values.

    • lookupCol [Required]. The column number in tableArray which will be searched by the function. A lookupCol value of 1 searches the first column in tableArray, a lookupCol value of 2 searches second column in tableArray, and so on. Values must be greater than 0.

    • indexNum [Required]. The column number in the tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first column in tableArray, an indexNum of 2 returns the value in the second column in tableArray, and so on. Values must be greater than 0.

    • threshold [Optional]. A variable that specifies whether you want Flookup to find an exact match or an approximate match. It is the similarity level below which results WILL NOT be returned from tableArray. Values range from 0 (no filtering) to 1 (only exact matches returned). The default value is 0.6.

Key Points

    • QLOOKUP only returns the lookup result, not the corresponding percentage similarity. This means you only need a single column to display results.

    • QLOOKUP only returns the best match. There is no option to return other matches for each lookup item.

HFLOOKUP

=HFLOOKUP(lookupValue, tableArray, lookupRow, indexNum, [threshold], [rank])

Use the HFLOOKUP function to search any row of a range of cells, and then return a value from a specified row of that range. It works exactly like a transposed FLOOKUP function.

HFLOOKUP Parameters

    • lookupValue [Required]. The value to search for in any row of tableArray. The lookupValue argument can be a text entry, concatenated text entries, a single cell reference or a range of many cells.

    • tableArray [Required]. The range of cells that you want to retrieve data from. The values in a user-specified row of tableArray are systematically compared to lookupValue. These values can be text, numbers, or other values.

    • lookupRow [Required]. The row number in tableArray which will be searched by the function. A lookupRow value of 1 searches the first row in tableArray, a lookupRow value of 2 searches second row in tableArray, and so on. Values must be greater than zero.

    • indexNum [Required]. The row number in the tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first row in tableArray, an indexNum of 2 returns the value in the second row in tableArray, and so on. Values must be greater than 0.

    • threshold [Optional]. A variable that specifies whether you want HFLOOKUP to find an exact match or an approximate match. It is the similarity level below which results WILL NOT be returned from tableArray. Values range from 0 (no filtering) to 1 (only exact matches returned). The default value is 0.6.

    • rank [Optional]. A variable that specifies the rank of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to n (the nth possible match). The default value is 1 and results are ranked in order of descending percentage similarity.

SOUNDMATCH

=SOUNDMATCH(lookupValue, tableArray, lookupCol, indexNum, [rank])

Use the SOUNDMATCH function to lookup and return values based on their Soundex codes.

SOUNDMATCH also allows you to return alternative matches using the rank parameter. These matches range from 1 (best match) to n (the nth best match).

SOUNDMATCH Parameters

    • lookupValue [Required]. The value to search in the column of the tableArray. The lookupValue argument can be a text entry, a single cell reference or a range of many cells.

    • tableArray [Required]. The range of cells that contains the data. You can use a reference to a range. The values in the first column of tableArray are the values searched by lookupValue. These values can be text, numbers or anything else.

    • lookupCol [Required]. The column number in tableArray which will be searched by the function. A lookupCol value of 1 searches the first column in tableArray, a lookupCol value of 2 searches second column in tableArray, and so on. Values must be greater than 0.

    • indexNum [Required]. The column number in the tableArray argument from which the matching value must be returned. An indexNum argument of 1 returns the value in the first column in tableArray, an indexNum of 2 returns the value in the second column in tableArray, and so on. Values must be greater than 0.

    • rank [Optional]. A variable that specifies the rank of all the possible matches, that you want returned from tableArray. Values range from 1 (first possible match) to n (the nth possible match). The default value is 1 and results are ranked alphabetically (ascending order).

Using the Long Run Mode

    1. Head to Extensions > Flookup > Long Run Mode > SOUNDMATCH in your spreadsheet menu.

    2. Primary range: Select range of one or more columns and click "Get selected range".

    3. Secondary range: Select a separate range of one or more columns and then click "Get selected range".

    4. Index One: Enter the index of the column of values in "Primary range" that you want to search for in "Secondary range".

    5. Index Two: Enter the index of the column of values in "Secondary range" that you want to be matched with values from "Primary range".

    6. Click an empty cell where you want your results to be displayed.

    7. Click "Get fuzzy matches".

Key Points

  • When SOUNDMATCH LRM times out, the results that have been processed up to that point, will be displayed.