SOLVED

Using match function without the exact match

%3CLINGO-SUB%20id%3D%22lingo-sub-1949637%22%20slang%3D%22en-US%22%3EUsing%20match%20function%20without%20the%20exact%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1949637%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20worksheets%20containing%20names%20of%20the%20company.%20One%20has%20about%20500k%20entries%20and%20is%20used%20as%20a%20master%20list.%20The%20other%20has%2010k%20and%20is%20used%20as%20a%20reference.%20Now%2C%20I%20want%20to%20find%20out%20how%20many%20company%20names%20in%20the%20reference%20list%20I%20am%20missing%20when%20compared%20to%20the%20master%20list.%20I%20can%20use%20the%20match%20function%20to%20do%20this.%20However%2C%20in%20the%20master%20list%2C%20some%20company%20name%20has%20symbols%20before%20them%20or%20that%20they%20are%20written%20a%20bit%20differently.%20Should%20I%20use%20a%20different%20function%20in%20excel%20for%20this%20problem%20or%20is%20there%20any%20way%20I%20can%20solve%20this%20before%20using%20the%20match%20function%3F%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1949637%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1949675%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20match%20function%20without%20the%20exact%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1949675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888812%22%20target%3D%22_blank%22%3E%40MT_15%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20other%20fields%20you%20can%20use%20to%20distinguish%20the%20companies%2C%20phone%20%23%2C%20web%20address%2C%20ID%2C%20etc.%3F%3C%2FP%3E%3CP%3EThe%20best%20alternative%20to%20a%20wildcard%20match%20is%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D15011%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFuzzy%20Lookup%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20allow%20you%20to%20control%20how%20exact%20the%20match%20need%20to%20be.%20If%20you%20have%20more%20than%20one%20column%20for%20the%20lookup%20to%20reference%2C%20this%20can%20increase%20the%20accuracy%20of%20the%20final%20pull.%20See%20below%20a%20screenshot%20test%20of%20how%20it%20works.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_1-1606931957926.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237378i67080091E76EE821%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_1-1606931957926.png%22%20alt%3D%22adversi_1-1606931957926.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1949959%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20match%20function%20without%20the%20exact%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1949959%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20for%20your%20reply.%20I%20don't%20really%20have%20any%20other%20fields%20for%20reference.%20The%20master%20list%20only%20has%20one%20column%20that%20is%20the%20500k%20company%20names.%20The%20second%20list%20has%20more%20than%201%20but%20I%20don't%20think%20they%20are%20gonna%20be%20useful.%20Anyway%2C%20this%20seems%20to%20be%20what%20I%20am%20looking%20for.%20Again%2C%20thank%20you%20so%20much%20for%20your%20help!%20I%20will%20update%20this%20reply%20to%20let%20you%20know%20how%20it%20goes.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

4 Replies
Best Response confirmed by MT_15 (Occasional Contributor)
Solution

@MT_15 

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 Fuzzy Lookup

 

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.

adversi_1-1606931957926.png

 

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!

@MT_15 

Glad to help - here's a good reference 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

@adversi 

Thank you! I was able to use it after watching a youtube video, the add-on is fairly simple but it takes a long time to load with big data sets.