+ Reply to Thread
Results 1 to 6 of 6

Fuzzy match formula to match partial email string in another list?

  1. #1
    Registered User
    Join Date
    02-27-2008
    Posts
    16

    Fuzzy match formula to match partial email string in another list?

    Hi all is it possible to get some help please with matching email addresses from one column to another please?

    Column A contains email addresses as entered by several users. Column B contains email addresses that need to be excluded.

    BUT, sometimes column A isn't always entered in the correct format so doesn't match anything in column B when doing an exact match so I need a way to match it based on a similar string contained in both columns. Just the formula to use will help.

    I've attached an example list here:

    User entered emails (sample)

    ianstudd@btopenworld.com
    leegoacher@aol.com
    leegoacher@aol.com
    markmoore517@gmail.com
    markmoore517@gmail.com
    annstudd@outlook.com
    rasalthouse61@gmail.com
    juliefranco1@outlook.com
    debmurray1975@yahoo.co.uk
    ksastewart@googlemail.com
    ksastewart@googlemail.com
    miss-nicole1994@hotmail.com
    mistybirrell@hotmail.com
    jmr@boysandmaughan.co.uk


    Emails to exclude (sample)

    kerry.ann128@hotmail.co.uk
    jessicarichards82@outlook.com
    johnevans72@gmail.com
    lewiskeller96@gmail.com
    lewiskeller@gmail.com
    aleonards@talktalk-mail.com
    acunningham@plusnetmail.co.uk
    ali_white@plusnetmail.co.uk
    aswift@talktalk-mail.com
    ambers@plusnetmail.co.uk
    aroberts@btinternet.uk.com
    apowell@btinternet.uk.com
    Hanson-a1452@plusnetmail.co.uk
    aanderson@plusnetmail.co.uk
    acarter@talktalk-mail.com


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,095

    Re: Fuzzy match formula to match partial email string in another list?

    you didn't mention in your profile which version of excel you have though you loaded a xlsx version so, have you looked into the fuzzy lookup download available free from Microsoft?
    https://www.microsoft.com/en-us/down....aspx?id=15011
    it sounds like it would meet your needs.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    02-27-2008
    Posts
    16

    Re: Fuzzy match formula to match partial email string in another list?

    Hi there we're on 2010 at work but unable to add any extensions due to IT restrictions.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,095

    Re: Fuzzy match formula to match partial email string in another list?

    It is a good application for inexact matches. My company has a pretty strict download policy and firewall but because it is from Microsoft I didn't have any problems downloading it to my pc for what it is worth.

  5. #5
    Registered User
    Join Date
    02-27-2008
    Posts
    16

    Re: Fuzzy match formula to match partial email string in another list?

    OK cool thanks I will ask IT on Monday if they will install it for me. Is it fairly easy to use?

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,095

    Re: Fuzzy match formula to match partial email string in another list?

    I just read up on it and followed the prompts. You have to set up two tables that you are comparing, you use control L, NOT the name manager. Then you click on the fuzzy lookup tab and it will open a box, you select the tables to compare, you pick the items you are comparing and mark the equivalents, set the threshold (percent of match you are willing to accept) then hit go.
    when downloaded it will appear across the top of your excel like the other tabs like Home, Insert, Page Layout etc.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple Partial String Match in Index Formula
    By atif574 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2017, 02:48 AM
  2. IF Partial Fuzzy Match then Calculate Percentage
    By CHRISOK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-20-2017, 05:04 PM
  3. Match a partial string with the match function
    By filterengineeer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-27-2017, 09:11 PM
  4. Replies: 3
    Last Post: 06-17-2015, 02:42 PM
  5. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  6. Trying to match partial string with another string and give position or cell
    By pat3white in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-01-2013, 10:07 AM
  7. [SOLVED] Vlookup, Match (Search or Find) partial string within string in a Cell
    By dluhut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2013, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1