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
Open the sidebar
Go to Extensions > Flookup Data Wrangler > Lookup and Merge > FLOOKUP in your spreadsheet menu.
Select the mode to run
Return best match only: Returns the best match for every lookup value.
Return best match and similarity: Returns the best match for every lookup value with its respective similarity.
Return best match and row index: Returns the best match for every lookup value with the row index in which it is located.
Merge data from rows with matches: Returns the selected Secondary range data from each row in which a match is found e.g. Assuming you selected range B2:E1000 in step #4 below and, while searching for matches of A2 in column C, a match is found in cell C50. In such a scenario, data from row B50:E50 will be returned.
Match long text entries: Compares text entries on a word-level of similarity. It is suited for long text entries like sentences and paragraphs, and it returns the best match for every lookup value with its respective similarity.
Select the range containing the lookup value
Highlight a single column of data e.g. A1:A100 and click "Grab selected range" to read it into Primary range.
Select the range where the lookup is potentially located
Highlight a separate range of one or more columns e.g. B1:D100 and click "Grab selected range" to read it into Secondary range.
Specify the lookup column
Enter the Lookup_column index. This is the column in "Secondary range" to compare with values in "Primary range". Therefore index 1 will compare the first column in B1:D100, index 2 will compare the third column in B1:D100 etc.
Specify the return column
Enter the Return_column index. This is the column in "Secondary range" from, which matches are to be returned. Therefore index 1 will return values from the first column in B1:D100, index 2 will return values from the third column in B1:D100 etc.
Set the level of similarity
Enter the minimum percentage similarity between values in "Primary range" and "Secondary range". The default value is 0.8.
Specify the output location
Click any empty cell where you would like your results to be displayed. This cell should be in any sheet inside that same spreadsheet.
Get fuzzy matches
Click the "Get fuzzy matches" button to finish.
-----
Notes About Matching and Merging Data
Lookup_column takes only one column. If you select more than one column, only the left most column of that range will be analysed.
In order for FLOOKUP to return a match and its respective percentage similarity or its respective position in the Secondary range, you must leave two empty columns for the results to be displayed. If you forget to do this, your data will be overwritten.
For better results from the Match long text entries mode, be sure to correct any spelling errors in them as a preprocessing step. You can find the function under Tools > Spelling > Spell check. You also need to leave two empty columns in this mode.
-----
FLOOKUP Custom Function
To use the custom function variant of FLOOKUP e.g. =FLOOKUP(A1:A100, B1:B100, 1, 1, 0.65), click here.
SOUNDMATCH
Open the Sidebar
Go to Extensions > Flookup Data Wrangler > Lookup and Merge > SOUNDMATCH in your spreadsheet menu.
Select the range containing the lookup value
Highlight a single column of data and click "Grab selected range" to read it into Primary range.
Select the range where the lookup is potentially located
Highlight a separate range of one or more columns and click "Grab selected range" to read it into Secondary range.
Specify the lookup column
Enter the index of the column in "Secondary range" to compare with values in "Primary range". Only the leftmost column will be analysed if more than one column is selected.
Specify the return column
Enter the index of the column in "Secondary range" from which you want values to be returned.
Specify output location
Click an empty cell within that same spreadsheet to mark the position where the results should be displayed.
Get fuzzy matches
Click the "Get fuzzy matches" button to finish.
-----
SOUNDMATCH Custom Function
To use the custom function variant of SOUNDMATCH e.g. =SOUNDMATCH(A1:A100, B1:B100, 1, 1), click here.