Standard Excel lookups are perfectionists that fail on typos or extra spaces. While you could hack a solution using nested XLOOKUP, TRIM, and LOWER functions, it’s brittle and hard to maintain. Power Query fuzzy matching is the smarter, “close enough” way to work.
Power Query uses a sophisticated fuzzy matching algorithm to calculate a similarity score between two strings, bridging the gap between “Microsoft” and “Mcrosoft” without a single manual edit. This means you can merge messy exports with cleaner master databases in seconds.
The full Power Query experience—including creating and editing queries—is natively available in Excel for Microsoft 365 (desktop), Excel for the web (Business and Enterprise plans), and Power BI.
The goal: A tale of two tables
Imagine you have a sales report (T_Sales, orange) full of manual entries, and you need to pull the sales region from a master table (T_Master, green).
However, because of typos and different naming conventions in T_Sales, a standard XLOOKUP would return #N/A for pretty much every lookup. So, we need Excel to look at the names in T_Sales, find the closest match in T_Master, and return the region, even if the spelling is slightly off.
To handle specific business shorthand that no algorithm can guess (like “MSFT”), you also need a small bridge table (T_Abbrev, blue) with From and To column headers. Make sure this table is ready before you begin.
Step 1: Import your data using connections
Before letting the algorithm loose, you need to bring all three tables into the Power Query engine. Start by ensuring your data ranges are formatted as Excel tables (Ctrl+T). Then, import the first table by selecting any cell in the table and clicking Data > From Table/Range.
Related
If You Don’t Rename Tables in Excel, Today’s the Day to Start
What’s in a name? Well, quite a lot.
To avoid cluttering your workbook, don’t click the standard “Close & Load” button after importing each table to Power Query. Instead, click the down arrow and select “Close & Load To.”
Then, in the Import Data dialog, select “Only Create Connection,” and click “OK.”
After you’ve repeated this process for all three tables, you’ll see them as connections only in the Queries & Connections pane (which opens automatically after you create the first connection), ready for the merge.
Step 2: Merge your tables to begin the cleanup
To begin the cleanup, right-click “T_Sales” in the Queries & Connections pane and select “Merge.”
Then, in the Merge dialog, select “T_Master” as the second table, and in both table previews, select the “Name” column. Essentially, you’re telling Excel that you want to smash the T_Sales and T_Master tables together, and the Name column is what connects them.
Now, check “Use fuzzy matching to perform the merge” to tell Excel that some of the connections aren’t perfect due to typos or inconsistent naming conventions. Under the hood, the Ignore Case option is checked by default, meaning “MICROSOFT” will correctly match “microsoft” without any extra steps. For the Join Kind, stick with “Left Outer”—this ensures you keep every sales transaction while simply tagging on the matching regional info.
Related
How You Can Use Wildcards in Microsoft Excel to Refine Your Search
Find partial matches in an instant.
Step 3: Dial in the similarity threshold
OK, so you’ve told Excel which tables you’re merging, the connecting variable, and that the matches aren’t exact. Now, you need to set the threshold.
Click the arrow next to “Fuzzy matching options.”
The similarity threshold is a scale from 0.00 to 1.00, where 0.00 matches all values with any level of similarity, and 1.00 matches only exact values. Notice in the screenshot above that three of the six rows are matched. This is because Power Query defaults to a 0.80 similarity threshold. At this level, the engine is strict—it easily catches “Microsoft Inc” but fails on more aggressive typos like “Appel” or “Gogle.”
In this case, a threshold of 0.50 is low enough to catch “Appel” and “Gogle” without accidentally matching unrelated words. It finds five out of six rows, leaving only the “MSFT” abbreviation unmatched.
Step 4: Apply the transformation table for abbreviations
No algorithm is smart enough to know that “MSFT” means Microsoft. This is where the T_Abbrev table comes into play—in the Transformation Table drop-down menu, click “T_Abbrev,” and this forces the engine to treat your manual overrides as perfect matches. Once selected, your match count at the bottom will jump to a perfect six of six.
Step 5: Clean up and summarize your final report
When you click “OK,” you’re taken to the Power Query Editor with a new column full of table results.
Click the “Expand” icon in the column header, uncheck “Use original column name as prefix” to prevent clunky headers like T_Master.Name, and click “OK.”
You can see that the names are now standardized in a new Name.1 column and the regions are appended in a new Region column.
From here, remove the original messy Name column (right-click the column header and select “Remove”), click and drag the new Name column to the left so that it’s the first column in the table, and rename it (double-click the column header) to something like Official Name. While you’re there, make sure that the correct data types are assigned to each column by clicking the icons in the column headers.
Related
How to Clean Up and Import Data Using Power Query in Excel
Don’t overlook this amazing Excel tool!
Because this process tags every individual transaction, you end up with multiple rows for the same company. To turn this into a clean report, in the Transform tab, click “Group By.”
Then, in the Group By dialog, select the “Advanced” radio button at the top. This lets you group by multiple columns at once. In this case, we want to group by the Name and Region columns. To do this, for the first grouping, select “Official Name.” Then, click “Add grouping,” and select “Region.”
Now, set the calculation. In the New column name field, type Total Sales, in the Operation field, select “Sum,” and in the Column field, select “Sales.”
When you click “OK,” all repeated rows are aggregated.
Once you’ve reviewed the results to confirm that all the data appears as expected, click “Close & Load” in the Home tab.
Now, your beautifully summarized, perfectly spelled data appears in a fresh Excel sheet. Here’s a before-and-after screenshot that shows you the benefit of using Power Query’s fuzzy matching to merge and clean up messy datasets in Excel.
Standard lookups are too rigid for the reality of messy data. By using fuzzy matching, you trade manual data entry for a scalable, automated system that understands intent rather than just syntax. This is just one of many ways to leverage Power Query tools to tidy up messy spreadsheet data—once you get started, you won’t be able to stop!
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
