+ Reply to Thread
Results 1 to 2 of 2

Compare two columns for matches and add values into third column based on matches.

  1. #1
    Registered User
    Join Date
    09-26-2013
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    1

    Compare two columns for matches and add values into third column based on matches.

    We are updating customer records at my company and I have two lists with contact information where I am working with the old list and then the new list.

    The problem that I am having is that the new list (containing just under 5000 entries) does not have the email info. The old list contains over 5100 entries and therefore there is a mismatch so I cannot simply copy the email info over to the new list for obvious reasons.

    Both lists contain unique company reference numbers in Row A but only the old list has email addresses in Row C. Is it possible to use a formula that automatically transfers email addresses over from the old list to the C column of the new list based on matches of the reference numbers?

    Basically the old list looks like:

    Column A Column B Column C
    Reference Number Company Name Email

    REF0001 Company A email1
    REF0002 Company B email2
    REF0003 Company C email3

    Etc.
    Sometimes there are multiple offices of the same company on the list.

    So based on this is it possible to look for a match in every entry in the new list. The problem that I see is that the entry in A3246 in the new doc fx might have a corresponding reference number at A3567 in the old doc. Can the email address in C3567 be copied over to C3246 in the new doc?

    Can this formula then be dragged to apply to every single entry on the new list so all email addresses get copied over. Another problem is that there are only around 4100 emails on there so every firm would not have a registered email address. The formula would have to ignore these blank entries.

    I hope someone can help.

    Thanks

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Compare two columns for matches and add values into third column based on matches.

    So, assuming you have your old list A2:c5100, and your new list of reference numbers is at F2:F5000

    then, in G2:

    =iferror(vlookup(F2,$A$2:$c$5100,3,0),"")

    and just copy downwards

+ 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. Replies: 0
    Last Post: 08-11-2012, 03:25 PM
  2. Compare 2 other columns if first one matches
    By dclive in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2011, 05:56 AM
  3. Replies: 0
    Last Post: 09-01-2010, 02:47 PM
  4. Compare two columns and add third column based on matches
    By sa02000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-30-2008, 10:06 AM
  5. [SOLVED] I need to compare to columns and indicate the matches in another
    By IFIXPCS in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-22-2006, 01:10 PM

Tags for this Thread

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