Which rows in these two lists are the same company?
Your CRM says Acme Corporation. Billing says Acme Corp. Somewhere further down, one list has Jhon Smith Plumbing LLC and the other has John Smith Plumbing LLC, and VLOOKUP returns nothing for either one. This template is the reconciliation everyone has done by hand at least once: two customer exports, sixteen rows each, and a similarity score on every pair so you can stop squinting.
Operations Intermediate Fuzzy Lookup free
After you install, this is the model to open.
Match Two Customer Lists That Don't Agree
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
One click scores all 256 pairs and hands back the best billing match for each CRM row. Every figure below is what the engine actually returns on this sheet.
- Name pairs a person would compare
- 256 16 CRM rows by 16 billing rows
- Found at the default threshold
- 12 of the 13 real matches
- Lookalikes correctly rejected
- 2 of 2 plus the row with no counterpart
- Real matches lost at threshold 0.80
- 4 including both reordered names
At the default 0.50 the engine finds 12 of the 13 real matches and correctly refuses all three rows that should stay unmatched, including both lookalikes: Northline Freight against Northline Software, and Pinnacle Dental against Pinnacle Roofing. Word order is not the problem people expect it to be, because matching works on tokens rather than letters: Smith & Sons, Ltd. against Sons Smith Ltd scores a perfect 1.000. The one it misses is Vantage Holdings LLC against Vantage Holdings, L.L.C., which scores only 0.324 because the periods shatter the suffix into single letters. That is the real lesson: punctuation inside an abbreviation hurts far more than a typo or a rearranged name. Raising the threshold to 0.80 does not fix it either, it costs you four more true matches, so the better move is to strip periods from your data before you match.
The model
Two exports of the same customer base, shuffled so they cannot be lined up by row. Every difference in the sheet is one a real export actually produces.
| CRM export (columns A:B) | 16 customers, CR-1001 to CR-1016 |
| Billing export (columns D:E) | 16 accounts, BL-4001 to BL-4016, out of order |
| Comparisons by hand | 256 (16 names by 16 names) |
| True matches planted | 13 of the 16 CRM rows |
| Kinds of mismatch | 3 suffix drift, 4 typos, 3 punctuation, 2 word order, 1 spacing |
| Lookalikes that are different companies | 2 (Northline Freight vs Northline Software, Pinnacle Dental vs Pinnacle Roofing) |
| CRM rows with no counterpart | 1 (Ellery Fabrication Works) |
| Similarity threshold | 0.50 (0 = nothing in common, 1 = identical) |
| Matches returned per row | 1 (best match only) |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- What do the next four quarters of demand actually look like?Seasonal Demand Forecast
- What does website downtime really cost us per year?What Does an Hour of Website Downtime Cost?
- Is the feasibility study worth it before you bid?Should We Pay for a Feasibility Study Before Bidding?
- Should we pay the overtime or take the late penalty?Overtime vs Late Penalty Scheduler
- What order should you drive your stops in?Shortest Route Finder
- If we build one 50% bigger, what will it actually cost?What Does Twice the Capacity Cost?
Every model like this one, and the method behind them: Statistics in Google Sheets.