How to Compare Two Excel Files and Find Matching Rows (Even When Data Doesn't Match Exactly)

You've got two Excel files. Same kind of data in each — maybe customers, maybe products, maybe suppliers. You need to figure out which rows in file A represent the same record as a row in file B, so you can update, merge, or reconcile them.

You try VLOOKUP first. It returns #N/A on most rows. You check the ones it missed and they're obviously the same record — just spelled slightly differently. So you try Power Query. It's slow, hard to configure, and still misses cases you can see with your eyes.

Comparing two spreadsheets to find "same" rows is one of the most common tasks in Excel, and none of Excel's built-in tools do it well when the data is even a little bit messy. Here's how to actually do it, in three steps.

Why Exact-Match Tools Fail on Real Spreadsheets

Excel's comparison tools — VLOOKUP, XLOOKUP, MATCH/INDEX, conditional formatting, Remove Duplicates — all rely on the same thing: an exact string match between two cells. That works when the data is:

• Machine-generated with a shared ID column (order numbers, transaction IDs, SKUs from the same system)
• Cleaned and normalized to identical formatting
• From the same source

It fails as soon as the data was typed by humans, came from different systems, or was manipulated by anyone along the way. Here's what "the same row" looks like in real spreadsheets:

Every one of those pairs is the same company. VLOOKUP misses every one of them.

The problem gets worse when the "key" isn't a company name but something even messier: a product description ("32oz stainless steel water bottle" vs "Steel Bottle 32-ounce"), an address ("123 Main Street, Apt 4B" vs "123 Main St #4B"), or a person's name ("Robert J Smith" vs "Bob Smith").

What Fuzzy Comparison Actually Does

Fuzzy comparison scores how similar two strings (or two rows) are on a 0-100 scale, instead of returning a hard true/false. It handles the noise that trips exact-match tools:

• Case differences (Acme vs ACME)
• Punctuation and spacing (H&M vs H and M)
• Common suffixes (Inc, Corp, Ltd, LLC)
• Typos and misspellings (Johnson vs Jonhson)
• Word order (Smith, Robert vs Robert Smith)
• Abbreviations and expansions (St vs Street, Corp vs Corporation)

Under the hood it's usually one or a combination of the Levenshtein distance, Jaro-Winkler similarity, and token-based algorithms like token_sort_ratio. You don't need to know the algorithms — you just need to know that a score of 100 is identical, 90+ is essentially the same record with formatting differences, 75-90 is likely the same record with real variation, and below 60 is probably a different record.

Step 1: Prepare Both Files

Before comparing, do 5 minutes of prep in each file:

1. Identify the "key" columns. These are the columns that identify the underlying record. For customers it's usually name + email + phone. For products, name + SKU + brand. For addresses, street + city + zip.
2. Make sure both files have the same key columns available. If file A has phone but file B doesn't, that column can't help the comparison — leave it out.
3. Rename headers so equivalent columns have the same name. This isn't strictly required, but it makes the comparison configuration much simpler.
4. Trim whitespace (=TRIM(A2)) and lowercase (=LOWER(A2)) on all key columns. This alone catches the 10-15% of "matches" that were only different by case or trailing spaces.
5. Save both files as CSV. UTF-8, comma-delimited. Most fuzzy tools accept both XLSX and CSV, but CSV is more portable.

Step 2: Configure a Multi-Column Fuzzy Match

Upload both files to a fuzzy matching tool. The universal setup, adapted for different data types:

For customer / contact comparison

For company / account comparison

For product / SKU comparison

Set the match mode to whichever preset the tool offers — company, contact, product, or generic. That toggles normalization rules that matter for that data type (legal suffix stripping for companies, size code parsing for products, etc.).

Step 3: Read the Results

The tool produces a table with one row per file-A record, and for each one, the best-guess match from file B plus a similarity score. You can also usually see the top 3-5 candidate matches for the borderline cases.

Divide the results into buckets:

• Score 95-100: Same record. Different formatting only. Auto-accept.
• Score 85-95: Very likely the same. A few characters off, or a legal suffix difference, or a small typo. Worth a spot check but usually correct.
• Score 70-85: Requires human review. Real variations (rebrands, subsidiaries, common names) live here.
• Score 55-70: Probably different, but worth eyeballing to catch the occasional right answer.
• Score below 55: Different record. File-A row has no match in file B.

On a typical two-file comparison (say, 5,000 rows in each), the distribution looks like this:

• ~65% score 95+ — auto-matched.
• ~15% score 85-95 — quick review, mostly matched.
• ~10% score 70-85 — careful review, mixed results.
• ~10% score below 70 — treated as unmatched (row in A has no counterpart in B).

Total review time on a 5,000-row file: 20-40 minutes, versus days of manual VLOOKUP debugging.

What You Do With the Matched File

The output depends on why you were comparing in the first place. Common purposes and what to do next:

What About Power Query Fuzzy Merge?

Power Query added a Fuzzy Merge option a few years back, and for very simple cases it works. If you already have both files in Power Query and just need to find rough matches on a single column, it's a reasonable option.

Where it falls short (and why RevOps / finance / data teams end up outside Power Query):

• Single-column matching only — no weights across multiple columns.
• The similarity threshold is a single number for the whole match. You can't say "loose on name, strict on domain."
• Performance drops sharply above 100k rows.
• No domain-specific normalization — it treats "Inc." and "Incorporated" as unrelated tokens.
• No AI-assisted disambiguation for borderline pairs.

For a 500-row comparison on a single column, Power Query might be enough. For anything bigger or nuanced, a dedicated fuzzy comparison tool will finish in a fraction of the time and catch matches Power Query misses.

We wrote a longer piece on this: Excel Power Query Fuzzy Merge Not Working? Here's What's Actually Going Wrong.

Common Pitfalls to Avoid

Comparing a dirty file against a clean file without cleaning first. If file A has trailing spaces and file B doesn't, half your "misses" will just be the spaces. Trim and lowercase both sides before the comparison.

Setting the threshold too high. A cutoff of 95 will exclude a lot of real matches. Start at 70, look at the distribution, and adjust from there. It's easier to reject false positives in review than to hunt for false negatives.

Forgetting that one file may have multiple matches in the other. If file A has "Acme Corp" appearing once but file B has three rows for Acme (different branches, different years, different data quality), a good fuzzy tool returns all three candidates ranked. Look at the top-N candidates, not just the top-1.

Not saving the score column. Whatever you export, keep the similarity score. Six months from now when someone asks "why did we conclude these are the same record?" the score is the answer.

Treating fuzzy comparison as a black box. It's not magic. If your key column is one you'd struggle to match by eye, the tool will struggle too. Sometimes the fix is a better key column (add a domain or a zip code), not a better algorithm.

Wrapping Up

Comparing two Excel files is one of those tasks that sounds trivial in a meeting and takes a full afternoon in practice. The trap is that Excel's tooling — VLOOKUP, XLOOKUP, and Power Query's fuzzy merge — is calibrated for cleaner data than most spreadsheets actually contain.

Fuzzy comparison bridges that gap. Instead of demanding perfect matches on a single column, it looks at multiple signals, weights them, and returns a ranked, scored result. That means the same records get identified regardless of formatting, abbreviations, typos, or word order.

Prep both files, pick 2-4 key columns, run the match, review the 70-95 band. Twenty minutes for a job that used to take a day.

Related Reading

• Excel Power Query Fuzzy Merge Not Working? Here's Why
• Remove Duplicates in Excel When Names Don't Match Exactly
• Fuzzy Match on Multiple Columns With Weights