Formula that compares two sets of data

[CatsIgnoreMe]

Forgive me for a basic question, but I'm having trouble wrapping my mind around the best way to this. I have 5 columns of data from our existing records: School | Position | Name | Email | Phone and the same 5 columns of data from an updated source. I want Excel to compare to two sets of data. If there is a match, then a value of "Keep" (or any other value to represent "Keep"). If no match is found, the formula would return "New". Thank you for your help!

 

[Gerald Higgins]

Can you post a SMALL sample of your data - say half a dozen records from each data source ?
Make the data up if you don't want to post real data.
Be sure to include some examples that you consider to be a match, and some which are similar but not considered to be a match.

 

[CatsIgnoreMe]

I went to generatedata.com to create these sample records. The 01 set represents existing records in our database (from last year). The 02 set represents new data for this year. The unique data shared between the two sets will be the email address. In the 2nd sample set, three of the records are the same (compared to the first set). Two records are different.

01 Data Set
[TABLE="width: 717"]
<tbody>[TR]
[TD]Company[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Donec Tempus Ltd[/TD]
[TD]Daquan[/TD]
[TD]Collins[/TD]
[TD]Vestibulum@lobortisquispede.net[/TD]
[/TR]
[TR]
[TD]Ultricies Dignissim Consulting[/TD]
[TD]Hall[/TD]
[TD]Munoz[/TD]
[TD]sociis.natoque@Fuscealiquetmagna.net[/TD]
[/TR]
[TR]
[TD]Mauris Inc.[/TD]
[TD]Jeanette[/TD]
[TD]Jackson[/TD]
[TD]Nullam.feugiat.placerat@eu.co.uk[/TD]
[/TR]
[TR]
[TD]Ut Ltd[/TD]
[TD]Zeus[/TD]
[TD]Wooten[/TD]
[TD]at@velpede.net[/TD]
[/TR]
[TR]
[TD]Metus Vitae Velit Corp.[/TD]
[TD]Desiree[/TD]
[TD]Knowles[/TD]
[TD]lobortis.Class@at.edu
[/TD]
[/TR]
</tbody>[/TABLE]


02 Data Set
[TABLE="class: grid, width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Email[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Donec Tempus Ltd[/TD]
[TD]Daquan[/TD]
[TD]Collins[/TD]
[TD="colspan: 2"]Vestibulum@lobortisquispede.net[/TD]
[/TR]
[TR]
[TD]Ultricies Dignissim Consulting[/TD]
[TD]Hall[/TD]
[TD]Munoz[/TD]
[TD="colspan: 2"]sociis.natoque@Fuscealiquetmagna.net[/TD]
[/TR]
[TR]
[TD]Mauris Inc.[/TD]
[TD]Jeanette[/TD]
[TD]Jackson[/TD]
[TD="colspan: 2"]Nullam.feugiat.placerat@eu.co.uk[/TD]
[/TR]
[TR]
[TD]Aliquam Adipiscing Lobortis Corporation[/TD]
[TD]Rajah[/TD]
[TD]Parker[/TD]
[TD="colspan: 2"]et.magnis.dis@nibhlacinia.com[/TD]
[/TR]
[TR]
[TD]Maecenas Company[/TD]
[TD]Geraldine[/TD]
[TD]Hines[/TD]
[TD="colspan: 2"]In.mi@vulputate.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

OK, so are you ONLY comparing the email field, and ignoring any matches or mismatches between other fields ?

 

[CatsIgnoreMe]

OK, so are you ONLY comparing the email field, and ignoring any matches or mismatches between other fields ?

Yes

 

[Gerald Higgins]

OK. There are several ways to do this, here's one.

Let's make some assumptions about where your data is located.

Let's say data set 01 is in the range A1:D100, and data set 02 is in the range E1:H100.

To test whether an email address from data set 02 in cell H2 is found in the data set 01 in the range D1:D100, use something like this
=IF(ISERROR(MATCH(H2,D$1:D$100,0)),"NEW","KEEP")

You can then copy this down as far as required.

 

[CatsIgnoreMe]

Thanks for formula. It worked well! I appreciate the time you took in answering my question.