Forum Discussion
Using match function without the exact match
Hi all,
I have two worksheets containing names of the company. One has about 500k entries and is used as a master list. The other has 10k and is used as a reference. Now, I want to find out how many company names in the reference list I am missing when compared to the master list. I can use the match function to do this. However, in the master list, some company name has symbols before them or that they are written a bit differently. Should I use a different function in excel for this problem or is there any way I can solve this before using the match function? Thank you.
Is there any other fields you can use to distinguish the companies, phone #, web address, ID, etc.?
The best alternative to a wildcard match is https://www.microsoft.com/en-us/download/details.aspx?id=15011
This will allow you to control how exact the match need to be. If you have more than one column for the lookup to reference, this can increase the accuracy of the final pull. See below a screenshot test of how it works.
4 Replies
Is there any other fields you can use to distinguish the companies, phone #, web address, ID, etc.?
The best alternative to a wildcard match is https://www.microsoft.com/en-us/download/details.aspx?id=15011
This will allow you to control how exact the match need to be. If you have more than one column for the lookup to reference, this can increase the accuracy of the final pull. See below a screenshot test of how it works.
Thank you so much for your reply. I don't really have any other fields for reference. The master list only has one column that is the 500k company names. The second list has more than 1 but I don't think they are gonna be useful. Anyway, this seems to be what I am looking for. Again, thank you so much for your help! I will update this reply to let you know how it goes.
Edit: This has helped me so much when doing my work and saved me a bunch of times. Didn't know that such things exist in excel. Simply amazing! Again, thank you so much for helping me out!
Glad to help - here's a good https://www.credera.com/insights/excel-tips-fuzzy-lookup/ in case you are stuck. Basically, you have to make both data sets you have into tables in Excel to have the FuzzyLookup read it.
From there you can adjust the threshold %: the closer to 1, the closer the exact match is
