MATCH and MERGE
Overview
The Match and Merge tool (also known as Flookup) is the core of the Data Wrangler ecosystem. It searches for the best fuzzy matches between two datasets and returns the corresponding values, making it the ideal tool for reconciling inconsistent data or merging disparate spreadsheets.
To begin, open the tool by navigating to Extensions > Flookup Data Wrangler > Matching and Analysis > Fuzzy match by percentage or Fuzzy match by sound in your Google Sheets menu.
How to Use the Match and Merge Tool
-
Select the return mode
Choose how you want your results to appear:- Return best match only: Returns only the data found in the return column.
- Return best match and similarity: Returns the data and its similarity score (0.0 to 1.0).
- Return best match and row number: Returns the data and its original row position in the secondary range.
- Select the Primary range
Highlight the column of values you wish to search for and click Grab selected range. - Select the Secondary range
Highlight the table array or master database you wish to search within and click Grab selected range. - Specify the column indexes
Set the Lookup_column index (where to search) and the Return_column index (the data you want to retrieve). These indexes are relative to your Secondary range selection. - Specify output location
Click an empty cell in your spreadsheet to mark the starting position where the results should be displayed. - Get fuzzy matches
Adjust the similarity Threshold (default 0.75) and click Get fuzzy matches to finish.
Notes on Match and Merge
- The Threshold value determines the strictness of the match. A value of 1.0 requires an exact match, while lower values allow for increasing degrees of variation.
- When using Return best match and similarity, you can easily filter or sort your results by the similarity score to identify matches that might require manual verification.
- The tool processes your data and writes results directly into the sheet, starting from the specified output location and extending downwards.
Matching by Sound Similarity
For datasets where entries are phonetically similar but spelled differently (e.g. "Smith" vs "Smyth"), you can use the Fuzzy match by sound option. This mode uses phonetic algorithms to identify matches based on how words are pronounced.
- No Threshold required: Unlike percentage-based matching, sound similarity does not require a Threshold value.
- Available return modes: You can choose to Return best match only or Return best match and row number. Similarity scores are not applicable in this mode.
Visual Learning Resources
Labels might differ slightly but the steps remain the same.