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

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 that will be returned or merged. 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 index 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 index 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 (LRM) > 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 "Grab selected range".

    4. Secondary range: Select a separate range of one or more columns and then click "Grab 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

    • When adjusting "Index One", "Index Two" or "Threshold", please use the arrow buttons on the extreme right of each input field.

    • 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 data that will be returned or merged. The values in the user-specified column of "tableArray" are systematically compared to "lookupValue".

    • lookupCol [Required]. The column index 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 data that will be returned or merged. The values in the user-specified column of "tableArray" are systematically compared to "lookupValue"..

    • lookupCol [Required]. The column index 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 index 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 contains data that will be returned or merged. The values in the user-specified row of "tableArray" are systematically compared to "lookupValue".

    • lookupRow [Required]. The row index 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 index 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 data that will be returned or merged. The values in the user-specified column of "tableArray" are systematically compared to "lookupValue". These values can be text, numbers or anything else.

    • lookupCol [Required]. The column index 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 index 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, in ascending order.

Using the Long Run Mode

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

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

    3. Secondary range: Select a separate range of one or more columns and then click "Grab 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 adjusting "Index One" or "Index Two", please use the arrow buttons on the extreme right of each input field.

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