Excel Power Query Fuzzy Matching Explained

How-to basics · Updated November 21, 2026

Excel Power Query fuzzy matching finds likely matches between text values that are similar but not identical. In Excel for Microsoft 365 on Windows, open the Power Query Editor, choose Home > Merge Queries, select the matching columns, and turn on Use fuzzy matching to perform the merge; then review the results before using them.

What fuzzy matching does

A standard Power Query merge joins rows when the selected key values match exactly. That works well for consistent IDs, but it can miss records when names or labels contain a typo, an abbreviation, extra spaces, or a different word order. Fuzzy matching lets a merge treat sufficiently similar text values as potential matches.

For example, a customer table might contain “Northwind Traders” while a second table says “Northwind Trader.” An exact merge finds no match for those entries; a fuzzy merge may pair them. Power Query uses a similarity algorithm based on Jaccard similarity, which compares text parts rather than understanding a person’s intent or the meaning of a phrase. It is therefore a practical matching aid, not a guarantee that two records refer to the same real-world entity.

Fuzzy matching is an option for merging queries, not a worksheet function that you enter in a cell. It does not rewrite the original source values. Instead, it returns merge results in a query that you can inspect, expand, and refresh.

How to create a fuzzy merge in Excel

Start with two tables or queries that contain the text columns you want to compare. In Excel for Microsoft 365 on Windows, select a cell in a source table and choose Data > From Table/Range to open it in Power Query. Repeat for the other source if it is not already available as a query.

  1. In the Power Query Editor, select the first query. On the Home tab, choose Merge Queries to add the merge to that query, or Merge Queries as New to create a separate result query.
  2. In the Merge dialog, choose the second query from the lower list. Select the column to match in each preview. If you select multiple columns, select them in the same order in both tables.
  3. Choose the join kind that fits the task. A Left outer join keeps every row from the first table and adds matching rows from the second; other join kinds return different combinations.
  4. Check Use fuzzy matching to perform the merge, then open the fuzzy matching options if you need to adjust them. Select OK.
  5. Use the expand button on the resulting nested-table column to choose which fields from the second query to include. Review the merged rows, then choose Home > Close & Load to return the result to Excel.

The precise dialog labels can vary slightly between builds. The workflow above describes Power Query in Windows desktop Excel; Power Query features differ across platforms, so if you are using Excel for Mac or Excel for the web and do not see the fuzzy-matching checkbox, use a supported Windows desktop build rather than assuming an exact merge is fuzzy.

Fuzzy matching options and what they mean

The options help control which candidate pairs Power Query returns. They are not a substitute for checking important records, and changing a setting can produce many more or fewer matches.

  • Similarity threshold: This is a value from 0 to 1; the default is 0.8. A higher threshold is stricter, while a lower threshold accepts less similar candidates. A threshold of 0.8 does not mean that 80 percent of the characters are identical or that a result is 80 percent likely to be correct.
  • Ignore case: This allows differences in uppercase and lowercase to be disregarded. It is generally useful for names and labels unless letter case carries meaning in your data.
  • Match by combining text parts: This can help when the same text parts appear in a different order, such as “Lee, Jordan” and “Jordan Lee.” It does not make the merge understand every variation in names or word meaning.
  • Show similarity scores: When enabled, the output includes a score to help you sort and review candidate matches. Treat it as a ranking aid, not a probability of correctness.
  • Maximum number of matches: This limits how many candidates can be returned for a row. The default is one. Raising it can expose alternatives for review, but it can also multiply rows when the nested results are expanded.
  • Transformation table: A table with From and To columns can specify known equivalents, such as “Intl” and “International.” It gives the merge explicit vocabulary guidance; keep the mapping focused and check that each replacement is appropriate for the data.

A practical example: matching customer names

Suppose one query lists customers from an order system and another lists accounts from a service system. One source has “Green Valley Market,” while the other has “Green Vally Market.” If customer names are the only available key, a fuzzy merge may suggest that the misspelled entries belong together. You can expand the account ID and other useful fields from the second query to compare them.

Begin with a relatively strict threshold, leave the maximum number of matches at one, and turn on similarity scores if the option is available. Then inspect the results, especially rows with low scores or multiple plausible candidates. If the sources include a stable customer number, use that exact identifier instead of fuzzy-matching names. Names are not reliably unique: two different people can share a name, and one organization can have several similar trading names.

If you know a recurring abbreviation, a transformation table can make the intended equivalence explicit. For example, mapping “Co.” to “Company” may help only if that abbreviation is consistently used in the relevant field. A broad replacement can create false matches, so do not add mappings simply to make every row join.

Reviewing results and refreshing the query

A fuzzy merge returns candidate matches as part of the query result; it does not certify them. After expanding the second table’s fields, look for blank results, unexpected duplicates, and pairs that differ in meaningful ways. Sorting by a similarity score, when included, can help prioritize the least certain pairs for manual review.

Pay attention to the join kind. With a Left outer join, unmatched rows from the first table remain, usually with empty values from the second table. A fuzzy merge can also return multiple candidates if you raise the maximum number of matches. Expanding those candidates may create more than one output row for a single source record, which can change totals in later reports.

Once the query is set up, Power Query saves the transformation steps. When the source data changes, refresh the query to run those steps again. Refreshing can change which rows match if names, spelling, or the candidate data have changed, so important reports should include a review process rather than assuming that old match decisions remain valid.

Common problems and ways to reduce false matches

A lower threshold can recover misspellings, but it can also pair unrelated values. Short strings are especially risky: “ABC” may resemble several unrelated codes, while common words such as “Services” can make different company names look alike. Fuzzy matching is usually more useful on descriptive text than on short IDs or codes.

  • Normalize the data first: Trim unnecessary spaces, remove irrelevant punctuation, and make formatting consistent where appropriate. Cleaning can reduce noise without making the matching threshold overly permissive.
  • Choose the right key: Use a unique account number or other stable identifier for an exact merge whenever possible. A name-only merge should be treated as a fallback or a review step.
  • Check duplicates: Duplicate names in either source can make a plausible-looking match ambiguous. Investigate duplicates before expanding results or calculating totals.
  • Test on a sample: Compare matched and unmatched examples at more than one threshold. Look for both missed matches and incorrect matches instead of judging a setting only by how many rows it joins.
  • Keep a review trail: For consequential data, retain the original names and relevant IDs beside the proposed match so a person can verify it. Do not silently replace source values with fuzzy results.

Changing a text column’s type is not a magic fix. Fuzzy matching is intended for text columns; converting a numeric identifier to text may make it eligible as text, but fuzzy comparison of codes can be unsafe because a one-character difference may distinguish two legitimate records.

Fuzzy merge versus exact merge and other tools

Use an exact merge when keys are standardized and reliable. It is easier to interpret and less likely to create a false pairing. Use a fuzzy merge when human-entered text varies and there is no dependable exact key, or when you want Power Query to propose candidates for review. You can also clean the text first and then use an exact merge if the differences are predictable.

Fuzzy merge is different from appending queries. A merge matches columns across tables and brings related fields together; an append stacks rows from one table under another. It is also not the same as a worksheet lookup: Power Query performs the matching during query processing, while functions such as XLOOKUP look for a value according to their specified lookup behavior and do not offer Power Query’s fuzzy merge options.

Power Query is included with supported Excel editions, so fuzzy merge does not require a separate fuzzy-matching add-in. Availability and editing features vary by platform and build. For a repeatable Windows workflow, use the Power Query Editor and confirm that the fuzzy option appears in the Merge dialog.

Frequently Asked Questions

Is fuzzy matching available in Excel Power Query?

Yes. In supported Power Query builds, fuzzy matching is an option in the Merge dialog. In Windows desktop Excel, choose Home > Merge Queries and select Use fuzzy matching to perform the merge.

What is the default fuzzy matching threshold?

The default similarity threshold is 0.8, on a scale from 0 to 1. Increase it to be stricter or decrease it to allow looser candidates, then verify whether the resulting matches are correct.

Can Power Query fuzzy-match numbers or dates?

Fuzzy matching is for text columns. A number or date can be converted to text, but that does not make fuzzy comparison safe; for IDs and dates, prefer consistent formatting and an exact match.

Does fuzzy matching change the original data?

No. It creates merge results in a query and leaves the source values intact. You decide which fields to expand and load, and can refresh the query when its sources change.

Why did a fuzzy merge create duplicate rows?

A source row may have multiple candidates, especially if the maximum number of matches is raised. Expanding the nested results can create one output row per candidate. Check duplicates and match settings before using the output in summaries.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.