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

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. 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

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