How do I index Fuzzy Lookup queries? Is this a bug?

peacock

New Member
Joined
Dec 14, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I believe I am using Fuzzy Lookup correctly, but it is not behaving as expected.

I have a list of over 20,000 names of businesses. A large percentage of the records are duplicates that either have misspellings or extra words. I want to use the Fuzzy Lookup add-in to find the duplicates. However, I don't want Fuzzy Lookup to search all 20,000+ businesses in trying to find matches for any given business. That's because each business is also assigned an index number that represents a geographic area, and there aren't more than around 40 businesses that share any geographic area index number. So I want to limit my Fuzzy Lookup query to just the records that share the index number of any given record. The way I have gone about doing this is by creating two match criteria. One uses the default matching criterion on the "name" field. The other uses the "exact match" criterion on the index number field. However, the output list is still returning matches between records that do not share the same index number.

Here is a screenshot, showing the problem. Columns W ("column 2") and Z ("column 9") make up the Left Table, and columns AB ("column 2") and AC ("column 9") make up the right table. Note how I set an exact match on the two column 9s and a default match on the two column 2s. I expected to only get matches between records sharing the same index number in the column 9s, but as you can see, it has returned many records that do not share the same number (the number "1") found in column 9 of the Left Table. It is showing non-zero values for these records despite my "exact match" criterion.

I have tried searching google about this problem and have come across a couple other posts that are very similar, but there has never been a solution posted.

Some details: Windows 10 desktop, Microsoft Office 365.
lookup.JPG
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Maybe it doesn't like the fact that you've got identically named columns. Perhaps call columns AB and AC something else.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe it will help
imho, you should use Table1 (Ctrl+T) and Table2 (Ctrl+T) not Table1 again
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top