I have two lists of data - one is email addresses and ID numbers from our old database system, the other is email addresses with ID numbers from our new database system. The lists are not exactly the same and have enough differences to make this a pain to do manually. I basically want to find any matching email addresses between the two lists and return the corresponding old and new ID numbers for that person. Any suggestions? Thank you.
You should be able to use the vlookup formula to get the results you need, but without knowing how your two lists are setup is difficult to give the exact formula. Can you upload a sample workbook that shows the exact setup of your data?
Thanks for the response. Right now the lists are separate spreadsheets - data that I've exported from our old and new database systems. I've attached a sample workbook with separate sheets for OLDID and NEWID data. I'd like to end up with a list of First Name, Last Name, Email Address, OLDID, NEWID - if possible.
Sorry for the delay in responding. Based on the way the data is arranged on the OLDID and NEWID sheets we'll need to use the INDEX/MATCH formula instead of VLOOKUP.
The first step would be to create a 3rd tab can copy the First Name, Last Name and Email. You'll want to remove any duplicate email addresses if there are any. Then create an Old ID column and a New ID column. Your formula in the
Old ID column would be something along the lines of:
=IFERROR(INDEX(OLDID!C2:C20,MATCH(C2,OLDID!D2:D20,0)),"Not Found")
The part in Red is the range that contains the Old ID numbers. The part in Green is the range of email addresses on the OLD ID sheet
Your formula in the New ID column would be something along the lines of:
=IFERROR(INDEX(NEWID!C2:C18,MATCH(C2,NEWID!D2:D18,0)),"Not Found")
The part in red is the range of the New ID numbers. The part in green is the range of email addresses on the NEWID tab.
You'll have to adjust the formula based on the actual range size. Paste those formulas in row 2 of the OLD ID and NEW ID columns on the third sheets respectively, and copy down.
Last edited by mo4391; 06-07-2016 at 04:58 PM. Reason: fixed formula
Sorry for the delay in this response, but this worked brilliantly! Thank you so much!
The one change that I made was to add a $ to some of the cell references to lock them and keep them from incrementing when copying, i.e.
=IFERROR(INDEX(OLDID!C$2:C$20,MATCH(C2,OLDID!D$2:D$20,0)),"Not Found").
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks