How to Match Similar Company Names in Excel Using Lookup Tables
Question details
The user needs a reliable method to match similar company names or variants (like Ltd/Limited, Herbs/Herbals) in Excel spreadsheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Standardizing and matching data containing inconsistent variations of company names across different sheets.
- Observed behavior
- Relying solely on VBA Soundex produces incorrect phonetic matches and fails on standard abbreviations, necessitating a more precise alias matching method.
Ensure you have reviewed your dataset to identify the most common spelling variations and abbreviations used for the company names you want to match.
Use a Controlled Alias Table with VLOOKUP Formulas
Creating a dedicated lookup table for name variants is the most reliable way to standardize and match similar company names accurately.
While VBA functions like Soundex try to match names phonetically, they often produce false positives for similar-sounding names (like 'Herbs' vs. 'Herbals') and fail with abbreviations like 'Ltd' vs 'Limited'. A controlled alias table allows you to manually map every known variant to a single approved company name, ensuring 100% accuracy for your dataset.
Add a new worksheet to your Excel file and name it 'Note'. In column A, list all possible incorrect variants or abbreviations (e.g., 'Company Ltd', 'Company Pvt'). In column B, type the approved, standardized company name next to each variant.
Go to your main data sheet (e.g., 'Scenario'). In a blank column next to your unstandardized company names, enter the formula: =IFERROR(VLOOKUP(A2,Note!$A:$B,2,FALSE),"N/A - Update table"). This will search the 'Note' sheet for the variant in A2 and return the correct name.
If you need to cross-reference the corrected name with a secondary sales table, nest the formulas like this: =IFERROR(VLOOKUP(VLOOKUP(A2,Note!$A:$B,2,FALSE),Scenario!$F:$G,2,FALSE),"N/A - Update table").
Filter your main sheet for the 'N/A - Update table' result. Copy these unrecognized company names, paste them at the bottom of column A in your 'Note' sheet, and add their standardized names in column B.
Standardize Company Names Easily in WPS Spreadsheet
WPS Spreadsheet fully supports VLOOKUP, XLOOKUP, and advanced data validation to help you clean up and match messy company names effortlessly without relying on complex VBA scripts.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open your workbook containing the messy company names.
- 2. Create an Alias Sheet: Click the '+' icon at the bottom to add a new sheet, and type out your variations in Column A and standardized names in Column B.
- 3. Insert the VLOOKUP Formula: Return to your data sheet, type =VLOOKUP( in the target cell, select your alias table range, and press Enter.
- 4. Apply to Entire Column: Double-click the small green square at the bottom-right corner of the cell to drag the fill handle and standardize the entire column instantly.
Frequently Asked Questions
Why shouldn't I use Soundex or VBA phonetic matching for company names?
Soundex is a phonetic algorithm that groups words by how they sound. It often produces false positives for companies with similar phonetic footprints (like 'Labs' vs 'Lab' or 'Herbs' vs 'Herbals') and completely fails to match standard business abbreviations like 'Pvt' with 'Private'. An alias table provides exact control.
What is the best alternative to VLOOKUP for matching aliases?
If you are using a newer version of Excel or WPS Spreadsheet, you can use the XLOOKUP function. It is more robust than VLOOKUP because it defaults to an exact match, handles errors internally (removing the need for the IFERROR wrapper), and allows you to search right-to-left if your columns are arranged differently.
Can I use Fuzzy Lookup instead of a manual alias table?
Fuzzy Lookup add-ins can help identify potential matches by calculating the similarity between text strings, but they are prone to errors and require manual review for confidence thresholds. A controlled alias table requires more setup but guarantees 100% accuracy for known variations.
How do I maintain an alias table as new company variants appear?
Whenever your VLOOKUP formula returns the custom 'N/A - Update table' error, copy the new, unrecognized variant from your data sheet, paste it into the bottom of the variant column in your alias table, and map it to the correct approved name. The formula will automatically resolve.
