Yes. In Excel for Microsoft 365 on Windows, Power Query can find likely name matches with its fuzzy merge option; formulas can also flag names that match after simple cleanup. Use fuzzy matching when spellings differ, and treat its results as candidates to review rather than guaranteed identities.
What “similar names” means in Excel
Excel can compare names in several different ways, and the right method depends on what you mean by “similar.” An exact comparison finds identical text. A cleaned comparison can treat differences such as extra spaces or periods as unimportant. A fuzzy comparison can suggest that “Jon Smyth” and “John Smith” may refer to the same person, even though the strings are not identical.
That distinction matters because names often contain legitimate variations: a person may use a nickname, omit a middle name, change a surname, or have their name entered with different punctuation. A match tool can identify candidates, but it cannot establish that two records belong to the same person. For consequential decisions, verify with another identifier, such as an account number or email address.
Use Power Query for fuzzy name matching
Power Query is the most direct built-in option for comparing two lists when differences may include misspellings or alternate spacing. The steps below are for Excel for Microsoft 365 on Windows. Prepare each list as an Excel table: select a cell in the data and choose Insert > Table, or press Ctrl+T. Confirm that each table has a header, such as CustomerName, and give the tables distinct names on the Table Design tab if helpful.
- Load each table into Power Query. Select a cell in the first table and choose Data > From Table/Range. In Power Query Editor, select Home > Close & Load To and choose Only Create Connection. Repeat for the second table. Depending on the workbook and Excel build, the close-and-load choices may be presented in a dialog.
- Open the query for one list, then choose Home > Merge Queries > Merge Queries as New. In the merge window, choose the other query as the second table.
- Select the name column in the first preview and the corresponding name column in the second preview. Select the same join columns in both tables, and choose a join type. Left Outer is useful when you want to retain every row from the first list and bring in any candidate from the second.
- Check Use fuzzy matching to perform the merge, review the options, and select OK. Power Query creates a new query; it does not overwrite the source tables. Expand the resulting table column with its expand icon to display the matched name or other fields.
For example, a contact list containing “Katherine ONeil” might be compared with a customer list containing “Catherine O’Neil.” Fuzzy matching may return the latter as a candidate. Whether it should be accepted depends on the rest of the record; similar spelling alone is not proof of a match.
Adjust the fuzzy matching options
The merge dialog lets you control how broadly Power Query searches. A similarity threshold runs from 0 to 1: a higher threshold is more restrictive, while a lower one admits more possible matches. The default threshold is 0.8. It is a starting point, not a universal accuracy setting. A list with common short names may need a stricter threshold and careful review; a list with frequent transcription errors may benefit from testing a lower one.
- Ignore case: Treats upper- and lowercase differences as unimportant. This is generally useful for names.
- Match by combining text parts: Can help when the words in a name are arranged differently, but may create extra candidates.
- Show similarity scores: Adds a score to the output so you can sort or filter candidates for review. A score is a ranking aid, not a probability that the records identify the same person.
- Maximum number of matches: Limits how many candidates Power Query returns for each row. Keeping more than one can be useful when a name is common; limiting results to one can hide a plausible alternative.
- Transformation table: Lets you define known equivalent terms, such as a nickname and a formal name. Use it for documented variants rather than assuming that every short name has one fixed equivalent.
Power Query’s fuzzy merge uses a similarity method for text; it is not a promise that one particular typo distance or spelling rule will be used. Changing options can change the results, so test a sample with known correct and incorrect pairs before applying the process to a large list.
Use formulas to normalize names first
For straightforward cleanup, a helper column can make comparisons more consistent. Suppose the first list’s names are in A2:A100 and the second list’s names are in D2:D100. With the first name in A2, enter this in B2 to create a simplified key, then fill the formula down:
=LOWER(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",""),".",""))
This converts text to lowercase and removes spaces and periods after TRIM removes extra spaces at the beginning and end and reduces repeated spaces between words. For example, “J. Smith” and “j smith” both become “jsmith.” This is useful only if those differences should be ignored in your data. Removing spaces can also make genuinely different names collapse to the same key.
With the first list’s cleaned keys in B2:B100 and the second list’s names in D2:D100, put the same cleanup formula in E2 using D2 in place of A2, and fill down. With B2 as the first list’s key and E2:E100 holding the second list’s keys, enter this in C2 to check for a matching cleaned key:
=IF(COUNTIF($E$2:$E$100,B2)>0,"Matching cleaned key","No exact cleaned-key match")
Here, a result of “Matching cleaned key” means that at least one value in E2:E100 exactly matches B2 after the stated cleanup. COUNTIF is not a fuzzy matcher: it will not infer that “Jon” resembles “John.” Formulas are useful for repeatable cleanup, auditing, and simple exact checks, but they should not be presented as a substitute for fuzzy comparison.
Choose a method for your task
Pick the method based on the differences you expect and how much review the result can tolerate:
- Exact match: Use ordinary lookup or duplicate-checking methods when entries should be identical. It is clear and predictable, but misses typographical variants.
- Normalized exact match: Use helper columns when the only differences are known formatting details, such as case, spaces, or periods. Choose what to remove deliberately.
- Power Query fuzzy merge: Use it to generate candidates across two lists with spelling variations. It is especially useful when the same comparison needs to be refreshed after source data changes.
- Manual review or a dedicated data-matching tool: Consider this when records are numerous, names are highly variable, or the consequences of a false match are serious. Use additional fields and documented review rules.
For one quick comparison, a formula helper column may be enough. For a recurring reconciliation, Power Query is usually easier to refresh and inspect than a long set of hand-built formulas. Neither approach automatically knows which record is correct.
Common problems and ways to reduce them
Common names produce several candidates. “Alex Lee” may match many people. Include other fields in the review, retain multiple possible matches where appropriate, and avoid accepting the top name match without context.
Middle names, hyphens, and word order vary. Decide whether these differences should count before cleaning or merging. Removing punctuation can help with formatting inconsistencies, but stripping meaningful parts of a name may create false positives. A transformation table can help with known variants, but it should reflect your organization’s data rather than guess at equivalences.
Threshold changes have trade-offs. Lowering the threshold may find more misspellings but also adds unrelated names; raising it may reduce noise while missing valid variants. Make a small test set that includes known matches and known non-matches, then inspect the output at the threshold you plan to use.
Blank or inconsistent values distort the task. Filter blank names or decide explicitly how to handle them. Make sure the selected columns actually contain names and that each table has a clear header. If a value contains extra information, such as a title or notes in the same cell, split or clean that data before matching when practical.
A fuzzy result is not a confidence guarantee. Even when a similarity score is available, do not read it as a percentage chance that two rows refer to the same person. Use it to prioritize review, and keep a record of decisions if the matching process must be repeated or audited.
Availability, refresh, and alternatives
Power Query is part of Excel’s Get & Transform Data tools, but available features and controls can vary by platform and release. The instructions here target Microsoft 365 on Windows. Excel for the web does not provide the same full Power Query authoring experience, and Power Query capabilities on Mac differ from Windows; if the fuzzy option is missing, check the current feature availability for your Excel version or run the merge in desktop Excel for Windows.
After you load a query, you can update it when the source tables change by choosing Data > Refresh All. Refreshing repeats the query steps against the current source data; it does not decide whether a candidate match is true. Keep the source lists intact and review the merged output before using it to update records.
Other options include a carefully designed manual review, a database or customer-management system with record-linking features, or a third-party Excel add-in. An add-in may have separate licensing, privacy, and data-retention terms. Check those terms before sending personal or customer names to an external service. For modest lists, built-in Power Query and a documented review process may be sufficient.
Frequently Asked Questions
Can Excel find misspelled names automatically?
Power Query’s fuzzy merge can suggest candidates despite some spelling differences. It does not guarantee that every misspelling will be found or that a suggested pair is the same person, so review the results.
Does VLOOKUP find similar names?
VLOOKUP is designed for lookup matches, not general fuzzy name similarity. It can search for an exact value or use a wildcard in certain lookup modes, but a wildcard search is not the same as measuring how alike two names are. Use Power Query fuzzy merge for candidate matching.
Can I compare names in one column instead of two lists?
Yes. You can create two copies of the table or use Power Query to compare a query with itself, then review pairs. Be careful to exclude a row matching itself and to avoid counting the same pair twice. For a small list, sorting or filtering a normalized helper column may be simpler.
Can formulas find names that differ by one letter?
Excel has no standard worksheet formula that directly provides general fuzzy name matching. You can build specialized logic or use code, but that takes more setup and must define what counts as a match. For most users, Power Query is the more approachable built-in option.
Can I use fuzzy matching in Excel for Mac?
Power Query support and its available features vary on Mac, and the Windows fuzzy-merge controls may not be available in a given Mac release. Check the Data tab and Microsoft’s current Excel feature information for your installed version; if the option is absent, use Windows Excel or another reviewed matching method.
Related
- How to Unhide Rows in Excel With a Shortcut
- Unhide Columns in Excel: Keyboard Shortcuts and Steps
- How to Unhide Columns in Excel: Shortcut Key Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Remove Table, Keep Data? Steps and EffectsYes. In Excel for Microsoft 365 on Windows, select a cell in the table, choose Table Design >…
- How to Freeze Just One Row in ExcelTo keep the first row visible while you scroll in Excel, open View > Freeze Panes > Freeze…
- Excel Custom Number Format: Show Zero as Blank CellsTo display numeric zeros as blank in Excel without deleting their values, apply the custom number format 0;-0;;@…
- MS Word Watermark: How to Add, Edit, and Remove OneTo add a watermark in Microsoft Word for Microsoft 365 on Windows, open Design > Watermark and choose…
- Excel Shortcut Keys to Highlight Cells to the End of DataIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+End to select from the active cell through the worksheet’s…
- Excel Data Validation for Numbers: Rules and ExamplesYes. In Excel for Microsoft 365 on Windows, select the cells, go to Data > Data Validation, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.