How to Perform Fuzzy Matching in Excel (With Example)

by Zach Bobbitt Published on July 26, 2022

Often you may want to join together two datasets in Excel based on imperfectly matching strings. This is sometimes called fuzzy matching.

The easiest way to do so is by using the Fuzzy Lookup Add-In for Excel.

The following step-by-step example shows how to use this Add-in to perform fuzzy matching.

Step 1: Download Fuzzy Lookup Add-In

First, we need to download the Fuzzy Lookup Add-In from Excel.

It’s completely free and downloads in only a few seconds.

To download this Add-In, go to this page from Microsoft and click Download:

Then click the .exe file and follow the instructions to complete the download.

Step 2: Enter the Two Datasets

Next, let’s open Excel and enter the following information for two datasets:

We will perform fuzzy matching to match the team names from the first dataset with the team names in the second dataset.

Step 3: Create Tables from Datasets

Before we can perform fuzzy matching, we must first convert each dataset into a table.

To do so, highlight the cell range A1:B6 and then press Ctrl+L.

In the new window that appears, click OK:

The dataset will be converted into a table with the name Table1:

Repeat the same steps to convert the second dataset into a table with the name Table2:

Step 4: Perform Fuzzy Matching

To perform Fuzzy matching, click the Fuzzy Lookup tab along the top ribbon:

Then click the Fuzzy Lookup icon within this tab to bring up the Fuzzy Lookup panel.

Choose Table1 for the Left Table and Table2 for the Right Table.

Then highlight Team for Left Columns and Team for Right Columns and click the join icon between the boxes, then click Go:

The results of the fuzzy matching will be shown in the cell you currently have active in Excel:

From the results we can see that Excel was able to match each team name between the two datasets except for the Kings.

Excel also shows a Similarity score, which represents the similarity between 0 and 1 of the two names that it matched.

Feel free to adjust the minimum Similarity score within the Fuzzy Lookup panel to allow for matching between text values that have lower similarity scores.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel

Zach Bobbitt

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike. My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

2 Replies to “How to Perform Fuzzy Matching in Excel (With Example)”

Heiko Diaz says:

February 26, 2025 at 12:39 am

Hi,
could I use this fuzzy-matching also for a simple fuzzy search in Excel? I have tables showing vocals with and without accents – I would like to find an ‘a’ searching for an ‘á’ and vice-versa.

Reply
1. James Carmichael says:
  
  February 26, 2025 at 5:47 pm
  
  Yes! You can use **Fuzzy Matching in Excel** for a **simple fuzzy search**, including finding accented characters like `á` when searching for `a` and vice versa.
  
  ### **Approach 1: Using Power Query Fuzzy Matching**
  Power Query in Excel has a built-in fuzzy matching option that can help you match accented and non-accented characters.
  
  #### **Steps to Enable Fuzzy Matching in Power Query:**
  1. **Load Your Data into Power Query:**
  – Select your table and go to **Data → Get & Transform Data → From Table/Range**.
  
  2. **Merge Tables with Fuzzy Matching:**
  – If you’re comparing two tables, go to **Home → Merge Queries**.
  – Select the column that contains the text values (e.g., words with/without accents).
  – Check **Use fuzzy matching**.
  – Click **Advanced Options** and set the **Similarity Threshold** (try starting with 0.8 to allow small variations like accents).
  
  3. **Confirm Matches & Close:**
  – Click **OK**, then **Close & Load** to apply the fuzzy search.
  
  ### **Approach 2: Using Excel Functions with SUBSTITUTE**
  If you just need to **search for accented characters manually**, you can use `SUBSTITUTE` to replace accents and compare.
  
  #### **Example: Removing Accents in Excel**
  “`excel
  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, “á”, “a”), “é”, “e”), “ó”, “o”)
  “`
  This function replaces accented characters with their plain versions, allowing you to compare strings more easily.
  
  ### **Approach 3: Using VBA for a More Flexible Search**
  If you need a **dynamic fuzzy search**, you can use a simple **VBA function** to remove accents and compare values.
  
  #### **VBA Code to Remove Accents**
  1. Press `ALT + F11` to open **VBA Editor**.
  2. Click **Insert → Module**.
  3. Paste this code:
  “`vba
  Function RemoveAccents(ByVal Txt As String) As String
  Dim i As Integer
  Dim Accents As String, Replacements As String
  
  Accents = “áéíóúÁÉÍÓÚñÑ”
  Replacements = “aeiouAEIOUNN”
  
  For i = 1 To Len(Accents)
  Txt = Replace(Txt, Mid(Accents, i, 1), Mid(Replacements, i, 1))
  Next i
  
  RemoveAccents = Txt
  End Function
  “`
  4. Save and close the editor.
  5. Now, in Excel, use:
  “`excel
  =RemoveAccents(A1)
  “`
  This will return the text without accents, allowing you to perform a fuzzy search.
  
  ### **Conclusion**
  – **For simple searches:** Use `SUBSTITUTE` or the VBA function.
  – **For complex table matching:** Use **Power Query fuzzy matching**.
  
  Reply