How to Map Master Data with Excel Lookups and Fuzzy Matching
Question details
The user wants to build a robust master-data mapping process using exact lookups, fuzzy matching for similar records, and confidence rules for unresolved data.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating master data from multiple sources where records often share similar but non-identical naming conventions, making exact matching insufficient.
- Observed behavior
- Standard lookup functions like VLOOKUP or XLOOKUP fail to map records with minor discrepancies, necessitating a supplementary fuzzy matching and manual review process.
Ensure your master data is formatted as Excel Tables and that you have access to the Power Query tool via the Data tab, as standard formula functions cannot process text similarity.
Use Power Query Fuzzy Merge and Confidence Rules
Combine exact lookup formulas with Power Query's fuzzy merge feature to map data effectively and apply conditional logic for confidence scoring.
Since Excel lacks a built-in SIMILARITY function for cell formulas, Power Query is the standard method for mapping similar but non-identical text strings.
By setting a similarity threshold and evaluating secondary attributes (like matching regions or branches), you can automate most of the mapping process and assign confidence levels to the results.
Start by using XLOOKUP or VLOOKUP in your worksheet to map all records that share an exact match across your data sources.
Select your tables containing the remaining unmatched records, go to the 'Data' tab, and click 'From Table/Range' to load them into the Power Query Editor.
Click 'Merge Queries' on the Home ribbon. Select the columns to match, and check the box for 'Use fuzzy matching to perform the merge'. Expand the 'Fuzzy matching options' to set your Similarity Threshold (e.g., 0.80 for 80% similarity).
Add a Custom Column to evaluate secondary matches. Write a Power Query conditional statement (if/then/else) to assign a 90% confidence score if region and branch match with high name similarity, or a 60% score if the name similarity is lower but the region still matches.
Click 'Close & Load' to return the mapped dataset to Excel. Filter your new data table for records with low confidence scores or null values to review and map them manually.
Map Data Using Advanced Lookups in WPS Office
WPS Spreadsheet provides powerful data processing capabilities, including VLOOKUP, XLOOKUP, and complex conditional functions, allowing you to efficiently map master data and define confidence rules.
- 1. Open your datasets: Launch WPS Spreadsheet and open your master data files.
- 2. Map exact matches: Use the XLOOKUP function to instantly identify and align exact matches between your different data sources.
- 3. Apply conditional matching: For unresolved records, utilize a combination of IF and AND functions to verify secondary identifiers like region and branch to establish mapping confidence scores.
- 4. Highlight for review: Select the confidence score column, go to the Home tab, and use Conditional Formatting to highlight scores below your acceptable threshold for manual review.
Frequently Asked Questions
Does Excel have a built-in fuzzy matching function for formulas?
No, Excel does not have a native SIMILARITY formula function. To evaluate text similarity, you must use Power Query's Fuzzy Merge feature, write a custom VBA macro, or install a third-party fuzzy lookup add-in.
What is a good similarity threshold for fuzzy matching?
A common starting threshold is 0.80 (80%). This level is usually strict enough to prevent incorrect pairings (false positives) while remaining flexible enough to catch minor typos or abbreviations. You can adjust this threshold in the Power Query merge options.
How do I handle records that still don't match after fuzzy matching?
Records that fail both exact lookups and fuzzy matching must be isolated. You can use IF and AND logic to check if secondary identifiers (like region or branch) match to assign a partial confidence score, but these edge cases will ultimately require manual review.
Can I use XLOOKUP instead of VLOOKUP for exact matches?
Yes, XLOOKUP is highly recommended over VLOOKUP for exact matches. It is more flexible, defaults to exact match automatically, handles errors natively, and can search in any direction across your data table.
