How to extract matching data in 2 diferent work sheets

Posted on 2012-09-10
Last Modified: 2012-09-17
I have taken all my email addresses from my outlook and imputed them in to an excel spreadsheet. I have another spreadsheet with all my customers and their email address. I know my outlook email address are up to date and need to match the correct email address with the customer number. I can do a =VLOOKUP(E2,[Emails.xlsx]Sheet1!$A:$A,1,true) and it returns the matching emails but keeps adding the same email to the next 4 or 5 records until it finds another match. When I use False it returns #N/A for all records. Any help with this. Thank you.
Question by:jodyreid
    1 Comment
    LVL 17

    Accepted Solution

    It often happens that the data contain some extra spaces or other whitespace at the end which will prevent an exact match.  The approximate match (true in the last param) would work because it assumes the data is sorted and returns the previous record, which in this case is the correct one for the matches.  Try using the TRIM function on the data, then copy and paste special values back to clean it.  If that doesn't work, please post the sheet so we can take a look.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now