Power Query Fuzzy Match: How It Works and How to Use It

Yes. Power Query can fuzzy-match text when you merge queries, helping connect values that are similar but not identical, such as “Northwind Traders” and “Northwind Trading.” In Excel for Microsoft 365 on Windows, open the Power Query Editor, choose Home > Merge Queries, select the text columns, and turn on Use fuzzy matching to perform the merge.

What a fuzzy match does

A regular merge looks for equal key values. If one table contains “Maple Street” and another contains “Maple St,” an exact merge will not treat those entries as the same. Fuzzy matching compares text for similarity and can find likely counterparts despite differences in spelling, spacing, punctuation, or other small variations.

The result is still a merge between two queries: Power Query adds a column containing matching rows from the second query, which you can then expand. Fuzzy matching does not automatically rewrite your source data or establish that two similar values truly refer to the same person, company, or product. It proposes matches according to its settings, so review uncertain results before relying on them.

This is useful when joining customer lists, supplier records, product catalogs, or location names gathered from inconsistent sources. It is not a universal solution for every kind of matching: fuzzy merge is intended for text values, not for finding numerically close dates, amounts, or measurements.

How Power Query decides that text is similar

Power Query uses a similarity algorithm to compare text values. Its fuzzy matching uses the Jaccard similarity metric, which compares the shared content of two strings with their combined content. The similarity threshold controls how close a pair must be to count as a match. A higher threshold is stricter; a lower threshold allows more dissimilar pairs through.

The default threshold is 0.8. Think of it as a setting on a similarity scale, not as a guarantee that a match is 80 percent correct. A score or threshold does not understand business context: “Acme Ltd” and “Acme Limited” may be sensible matches, while two similarly spelled product names may represent different products.

Fuzzy matching is case-insensitive by default. Depending on the options available in the merge dialog, you can also control spaces, combine text parts, show similarity scores, limit the number of matches, and provide a transformation table. These settings affect how Power Query compares and returns values; they do not replace checking the underlying records.

How to create a fuzzy merge in Excel

Start with two queries in the Power Query Editor. For example, one query might contain a customer export and another a reference list with standardized customer names. Make sure each has a text column that can serve as the matching key.

In the Power Query Editor, select the first query. Choose Home > Merge Queries > Merge Queries as New to preserve both original queries and create a separate result. Choose Merge Queries instead if you intentionally want to add the merge to the current query.
In the Merge dialog, select the second query from the lower table selector. Click the corresponding key column in the upper preview, then click the matching key column in the lower preview. If matching on more than one column, select the columns in the same order in both tables.
Choose the join kind. Left outer is a common choice when you want to keep every row from the first query and bring in matching rows from the second. Other join kinds change which unmatched rows are retained.
Check Use fuzzy matching to perform the merge. Open the fuzzy matching options if you need a threshold or other settings, then confirm the merge.
In the resulting query, use the expand icon on the merged column to choose which fields from the second query to add. Review the output, then choose Home > Close & Load to return the result to Excel.

The directions above refer to Excel for Microsoft 365 on Windows. Excel for the web does not provide the full Power Query Editor authoring experience. Power Query capabilities and menus on Mac can differ from Windows, so if the fuzzy-matching checkbox is missing, use the Windows desktop editor or check the current feature support for your Mac version.

Fuzzy matching options to choose carefully

Similarity threshold: Keep the default initially, then test a stricter or looser value against known examples. Raising it reduces weaker matches but can miss legitimate variations; lowering it may increase false matches.
Ignore case: This is generally useful when capitalization is inconsistent. If capitalization has meaning in a particular identifier, do not assume case-insensitivity is harmless.
Ignore spaces or combine text parts: These can help with inconsistent spacing or text split into parts. They may also make distinct values harder to distinguish, so inspect results if the fields contain codes or compact names.
Maximum number of matches: A value of one can be appropriate when each source row should map to a single reference row. If the source value has multiple plausible counterparts, limiting results to one can hide ambiguity rather than resolve it.
Show similarity scores: When available, scores help you sort or filter results for review. They indicate the algorithm’s similarity assessment, not the probability that a match is correct.
Transformation table: Use a two-column mapping table to define known equivalents, such as “St.” to “Street.” This is useful for recurring, well-understood variants and makes the matching rules more explicit.

Example: matching inconsistent company names

Suppose the first query has “Harbor View Supply” and “Brighton Office Co.” A reference query contains “Harbour View Supplies” and “Brighton Office Company.” An exact merge may leave the first pair unmatched because of spelling and a plural ending, and may also miss the abbreviation in the second pair. A fuzzy merge can identify them as candidates, depending on the threshold and other options.

Now imagine the reference list also contains “Brighton Office Copy.” That name might be similar enough to create ambiguity. A fuzzy result should therefore be treated as a shortlist for review, not unquestioned proof. Expand the reference name and, if available, the similarity score. Check whether a supposedly unique key actually returns several rows before using the merged result for reporting.

For repeated cleanup, a transformation table can capture approved spelling variants. For one-off discrepancies, it may be safer to review the candidates and correct or map them deliberately. Keep a record of decisions when the merge affects customer counts, payments, compliance reporting, or another process where an incorrect link would matter.

Common problems and how to diagnose them

No matches appear: Confirm that the selected columns contain text and that the right columns were clicked in both previews. Try a lower threshold for diagnosis, but do not leave it low without checking the extra matches it creates.
Too many questionable matches appear: Increase the threshold, reduce the maximum number of matches where appropriate, or add a transformation table for known variants. Check whether the key is too short or generic to identify records reliably.
One row expands into multiple results: The second query may contain multiple similar values. A merge can return more than one matching row, so expanding the nested table can produce repeated rows in the output. Deduplicate or refine the reference data only when that matches the business rule.
Results change after a refresh: New source rows can introduce new candidates, and changes to data or settings can alter the output. Treat the merge as a repeatable transformation, but validate important matches after source changes.
The operation is slow: Fuzzy comparison can require more work than a straightforward exact join, especially with large lists or permissive thresholds. Remove unnecessary columns and rows before merging, use a meaningful key, and avoid returning several candidates if the task only needs one.
The merge appears to miss an obvious match: Check punctuation, abbreviations, spacing, and whether the values have been converted consistently. Fuzzy matching is not semantic understanding; two values that mean the same thing may still compare poorly if their text is very different.

Fuzzy merge versus exact lookup

Use an exact merge when the key is intended to be identical, such as an account ID, SKU, or carefully maintained code. Exact matching is easier to audit and less likely to attach a record to the wrong entity. Before reaching for fuzzy matching, consider normalizing predictable differences, such as trimming spaces or using a controlled mapping table.

Excel functions such as XLOOKUP are useful for worksheet lookups, but an ordinary XLOOKUP does not perform fuzzy text matching. Its match modes include exact and particular approximate or wildcard behaviors; those are not the same as Power Query’s similarity-based merge. Power Query is a better fit when you want a refreshable data-cleaning and joining process, while a worksheet lookup can suit a smaller, formula-driven task.

Fuzzy grouping is another related feature: it groups similar values within a table, whereas fuzzy merge connects similar values between two queries. Choose the operation based on whether you are consolidating variants in one list or joining two separate lists.

A reliable review workflow

For important data, build review into the process rather than treating the merge as a one-time button click. Keep the original text columns, load or inspect the candidate matches, and identify rows with no match or multiple matches. Test the settings on examples whose correct answers are already known, including both genuine variants and similar-looking values that must stay separate.

Once the rules are stable, document the threshold and any transformation-table entries. Recheck the results when a source system changes its naming conventions or when new records are added. If the task requires guaranteed identity, obtain or create a dependable identifier instead of relying on text resemblance alone.

Frequently Asked Questions

Is Power Query fuzzy match available in Excel?
Yes. In supported desktop versions of Excel with Power Query, use the Merge dialog and select Use fuzzy matching to perform the merge. The exact feature availability can vary by platform and version.

What is the default fuzzy match threshold?
The default similarity threshold is 0.8. It is a comparison setting, not an 80 percent confidence rating. Adjust it only after checking how the changed threshold affects real matches and false positives.

Can Power Query fuzzy match numbers?
Fuzzy merge is designed for text matching, not numeric proximity. For numbers, dates, or measurements, use an explicit range, rounding rule, or other logic that reflects what counts as a valid match.

Can I use fuzzy matching with more than one column?
You can select multiple columns as merge keys, selecting them in the same order in both query previews. Test the combination carefully: extra keys can narrow matches, and the fields should represent comparable information.

Does fuzzy matching change my original data?
No. It creates a merged query result; it does not silently correct the source values. You can expand the matched fields and build additional cleanup steps, but those are separate transformations in the query.

Why does a fuzzy merge return duplicate rows?
A source value may match multiple rows in the other query. Expanding those matches can repeat the source row. Inspect the candidate list and decide whether to refine the key, limit matches, or resolve duplicates using an explicit rule.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.