MATCH AND MERGE DATA BY TEXT SIMILARITY

Introduction to Match and Merge

These functions find and return desired matches based on their respective similarities to other text entries in comparison.

FLOOKUP has four different lookup function modes and they each compare text entries by their percentage similarity.

SOUNDMATCH on the other hand, compares text entries based on how similar they sound when pronounced in English.

FLOOKUP

1. 1. Head to Extensions > Flookup Data Wrangler > Lookup and merge > FLOOKUP in your spreadsheet menu.

   2. Select the mode you want this function to run from the drop-down menu [See Explanation Below].

   3. Primary range: Select a single column of data and click "Grab selected range".

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

   5. Lookup_Column: Enter the index of the column of values in "Secondary range" that you want to compare with values in "Primary range".

   6. Return_column: Enter the index of the column of values in "Secondary range" from which you want values to be returned.

   7. Threshold: Enter the minimum percentage similarity between the "Primary range" and "Secondary range" values, below which matches should not be returned. The default value is 0.6, meaning that matches with at least that level of similarity will be returned.

   8. Click an empty cell where you would like your results to be displayed. This cell should be in any sheet inside that same spreadsheet.

   9. Click the "Get fuzzy matches" button to finish.

FLOOKUP Modes Explained

1. 1. Return best match only: This returns the best match for every lookup value.

   2. Return best match and similarity: This returns the best match for every lookup value, paired with its respective similarity.

   3. Return best match and row index: This returns the best match for every lookup value, paired with the row index in which it is located.

   4. Return all matches for each item: This returns all possible matches for each lookup value in a comma separated format.

   5. Merge data from rows with matches: This will return all the selected data from each row in which a match is found, as opposed to returning data from rows in a single column e.g. Assuming you selected range B2:E1000 and, while searching for matches in column C, a match for cell A2 is found in cell C50. In such a scenario, data from row B50:E50 will be returned.

   6. Match long text entries: This compares text entries on a non-lexical level. It is suited for longer text entries e.g. sentences and paragraphs, and it returns the best match for every lookup value, paired with its respective similarity to the lookup value.

SOUNDMATCH

1. 1. Head to Extensions > Flookup Data Wrangler > Lookup and merge > SOUNDMATCH in your spreadsheet menu.

   2. Primary range: Select a single column of data and click "Grab selected range".

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

   4. Lookup_column: Enter the index of the column of values in "Secondary range" that you want to compare with values in "Primary range".

   5. Return_column: Enter the index of the column of key values in "Secondary range" from which you want values to be returned.

   6. Click an empty cell where you would like your results to be displayed, inside that same spreadsheet.

   7. Click the "Get fuzzy matches" button to finish.

You Might Also Like

• Preprocess Data by Text Similarity

• Fuzzy Matching Algorithms Explained

• How to Match and Merge Data in Google Sheets: An Flookup Tutorial