How to apply fuzzy lookup to find approximate match result in Excel?
Sometimes, we not only need to do exactly lookup, but also want to do a fuzzy lookup as below screenshot shown. Actually, in Excel, the Find and Replace function somewhat help you for fuzzy lookup.
I took the liberty of adding the tag excel-vba-mac since a follower of that tag might have a better idea than I about a solution. I added a pure-vba function which should work as a UDF (user-defined-function) on the Mac, but it might not be fuzzy enough for you. – John Coleman Sep 12 '16 at 10:27. The Fuzzy Lookup add-in from Microsoft works only in Excel 2007 - 2013 and can only compare data in two similar datasets formatted as tables. Our add-in works with any version of Excel 2019, 2016 - 2010, supports all Windows versions, and can handle any range of cells no matter its format. The Fuzzy Lookup Add-In for Excel was developed by Microsoft Research and performs fuzzy matching of textual data in Microsoft Excel. It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables.
Supposing you have a range A1:B6 as below screenshot shown, you want to fuzzy lookup string “apple” without case insensitive or the “app” in this range. You can apply the Find and Replace feature to handle the job.
Look up in non-case sensitive
1. Select the range you want to look up, press Ctrl + F keys to enable the Find and Replace function, type the string you want to look up in the Find what textbox.
2. Click Options to expand the dialog, uncheck Match case option, but check Match entire cell contents option.
3. Click Find All, the strings not in case sensitive have been listed.
Look up part of string
1. Select range and press Ctrl + F keys to enable the Find and Replace function, and type the part string you want to look up in the Find what textbox, uncheck Match entire cell contents option, if you need also can uncheck Match case.
2. Click Find All, the cells which contain the string have been listed.
Fuzzy lookup one value or multiple values with a handy tool
If you need to lookup one values approximately or find out all approximate values at the same time, you can use the Fuzzy Lookup feature of Kutools for Excel.
| with more than 300 handy functions, makes your jobs more easier. |
After free installing Kutools for Excel, please do as below:
Lookup one value approximately
Supposing you want to lookup a value “app” in range A1:A7, but the number of different characters cannot be more than 2, and the number of characters must be large than 1.
1. Click Kutools > Find > Fuzzy Lookup to enable the Fuzzy Lookup pane.
2. In the popping pane, do as these:
1) Select the range you used to find, you can check Specified to fix the searching range.
2) Check Find by specified text checkbox.
3) Type the value you want to fuzzy search based on in the Text textbox.
4) Specify the searching criteria as you need.
3. Click Find button, then click the down arrow to expand the list to view the searching results.
Fuzzy lookup multiple values
Supposing you want to find all approximate values in a range A1:B7, you can do as below:
1. Click Kutools > Find > Fuzzy Lookup to enable the Fuzzy Lookup pane.
2. In the Fuzzy Lookup pane, select the searching range, and then specify the searching criteria as you need.
3. Click Find button to go to the searching result view, then click down arrow to expand the list.
Quickly split data into multiple worksheets based on column or fixed rows in Excel |
| Supposing you have a worksheet that has data in columns A to G, the salesman’s name is in column A and you need to automatically split this data into multiple worksheets based on the column A in the same workbook and each salesman will be splitted into a new worksheet. Kutools for Excel’s Split Data utility can help you to quickly split data into multiple worksheets based on selected column as below screenshot shown in Excel. Click for 60 days free trial! |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Other Operations (Articles)
Find largest negative value (less than 0) in Excel
To find the largest value from a range is very easy for most of Excel users, but how about finding the largest negative value (less than 0) from a data range which is mixed with negative and positive values?
Find the lowest common denominator or greatest common factor in Excel
All of us may remember that we are asked to calculate some numbers’ lowest common denominator or greatest common factor when we are students. But if there are ten or more and some big numbers, this work will be complicated.
Excel batch find and replace specific text in hyperlinks
In Excel, you can batch replace a specific text string or a character in cells with the another one by the Find and Replace function. However, in some cases, you may want to find and replace a specific text in hyperlinks, excluding other formats of contents.
Apply the reverse find or search function in Excel
Generally, we can apply the Find or Search function to look for a specific text from left to right in a text string by a specific delimiter. If you need to reverse the find function to look for the word starting at the end of a string as following screenshot shown, how could you do?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Fuzzy Lookup is an Excel add-in created by a Microsoft development team, and can be used for advanced search and comparison of column values in tables. Why does this serve for? For example, in practice, it often happens that, when entering a name and surname, an error occurs or there is no one-way entry. Fuzzy Lookup allows you to compare the column based on the similarity and display the corresponding results in the new table.
To use this plugin you need to first download and install it. Download is done on this link, and after you install, should add it to Excel. We do this by clicking on the File tab, and then selecting Options/Add-Ins. In the menu below, select the COM Add-Ins option, and then in the window that appears, select the option to activate. If you’ve done everything right, a new ribbon wil appear that contains only one option will appear – Fuzzy Lookup!
This Excel add-in compares data in structured tables. So if you’ve retrieved data from traditional tables, convert them to structured before you start comparing. In the example, we will use two tables: the first one is a list of employees in the personnel records, and the other records are list of accounts in a local coffee shop. Our goal is to get the information: how many employees really made the cost?
Excel Fuzzy Lookup Formula
We activate the option by going to the Fuzzy Lookup ribbon and clicking on the icon. On the right side will open a panel in which we’re adjusting the settings.
First, we need to choose which tables to compare and which columns we will compare. After selecting two tables (in our example: Table1, Table2) and the columns you compare (columns NAME and SURNAME in both tables), clicking on the icon that indicates the connection, we create the comparison criterion that appears in the Match Columns table. It is also possible to create more criteria if necessary. Then in the Output Columns list, we need to choose which columns from both tables should be displayed as a result. The Number of Matches should indicate how many results found in the source table should be displayed and by moving the slider Similarity Treshold we can adjust the sensitivity of the comparison criteria. When we have defined all the criteria, we need to go to the new worksheet and select a cell where Excel will display the results of the comparison. After that, we click the Go button and we get the required result.
Fuzzy Lookup Not Showing In Excel
We can repeat the procedure several times, with moving the Similarity Treshold slider until we get the desired results. Clicking on Undo is a step backwards, and clicking on the Configure button will open a window containing a number of additional options for adjusting the comparison criteria.