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.
Fuzzy lookup by Find and Replace
Fuzzy lookup one value or multiple values with a handy tool
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.
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.
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.
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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in