MATCH AND MERGE DATA BY TEXT SIMILARITY
Introduction to Match and Merge
These functions are designed to 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 v1: Returns the best match for every lookup value.
Return best match only v2: Returns the best match for every lookup value, but it is slightly faster than the previous mode.
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 rows where a match is found. For example, if range B2:E1000 is selected and A2 matches a value in column C (e.g., C50), data from B50:E50 is returned.
Match entries on a word-level: 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 to compare with values in step 3 above
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 "Secondary range" column to focus on for processing
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 "Secondary range" 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" that match values in "Secondary range". The default value is 0.85.
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 you are working on.
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 as a preprocessing step. You can find the function under Tools > Spelling > Spell check. You also need to leave two empty columns to accommodate results 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.