How to match names in Excel where spelling differ or vary

How to use Fuzzy Lookup in Excel to match names

Fuzzy Lookup addin in Excel

Think: Sometimes, we need to find a match in-between tables. In such cases we find the data using functions, like vLookup. This works when entries in both columns in the tables are completely identical. Sometimes that may not be the case. There may be spelling differences or some minor differences in the entries. Because of these, functions like vLookup would not work well.

Excel now has a downloadable AddIn called Fuzzy Lookup to help out with this issue. Fuzzy Lookup finds rows from one table which are identical to rows in another.

Fuzzy Lookup AddIn is available to download from Microsoft. Once you download, it will look like the picture in the slide above on your Excel.

Let us now look at an example to understand how Fuzzy Lookup works.

Fuzzy Lookup Step 01

Assume that you have a table consisting of Company Names and their respective turnover in a year. You need to find the turnover for some selected companies out of the list. This shown in the second table.

Here you may feel it is easy to go through the list and pick up each item one by one. But usually the lists are huge and it is not easy to find out such names when the list runs into hundreds of pages!

You may also like:

How to use Fuzzy Lookup
How to highlight row based on cell value
Excel Quartile Function
Undo & Redo Shortcut Excel
CHAR formula to inert degree symbol

Let us see how Fuzzy Lookup comes in handy in such cases.

Fuzzy Lookup Excel add in

  1. Click on the Tab Fuzzy Lookup
  2. Click on Fuzzy Lookup button to get a toolbar on the right like shown above.
    Now you need to feed tables in the toolbar on the righ
  3. Now you need to feed tables in the toolbar on the right.
    • The two tables you have created need to be converted into Tables in the Excel format.
    • For this, select the first table and press Ctrl + T on your keyboard.
    • Upon the prompt, press Enter.Repeat the same for the other table. Now we have two tables that are ready to be fed into the Fuzzy toolbar on the right.Use Fuzzy Lookup in Excel
  4. On the Fuzzy Lookup toolbar, select both tables.
  5. See the picture above. Select the bigger table first.
  6. Next, Select the corresponding column headers in both tables.
  7. Then create a relationship between them through the button in-between them.

Assign the threshold value

The next part is important: Assign the threshold value. Threshold value indicates the level of comparison essential to compare the two cells. The more the threshold value, the more exactness in comparison will be there. The less the threshold value, the less will be the exactness in comparison of the two cells.

  • If you need higher accuracy in matching, the value should be closer to 1.
  • If you need lower accuracy, i.e. you need to match very different words, then the value should be closer to 0.

Note: At threshold value 1 Fuzzy Lookup works the same as vLookup!!

Step 05 in Fuzzy Lookup

Result: Now, finally, click on the cell where you want the reports to be displayed. Then click on Go from the toolbar.

The results will be as displayed above.

Good Job!

We have learned in this tutorial how to match names in Excel where spelling differ or vary.

Have fun!!!

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018