Comparing 2 Spreadsheets, Highlight Certain Cells Based on Two Criteria

[oldfashionedgal]

I have tried to find a forum response that fits my particular scenario, and I have found similar ones but not quite what I need. I have two different spreadsheets which I would like to compare two pieces of criteria and only if BOTH criteria are met, then I would like cells in a particular column to highlight. First, I would like both spreadsheets to compare both the Name and ID# fields to see if there are any matches between new and existing customers. Then, if there are any customer matches, I would like it to take THOSE customers and highlight their Date Related cells that are less than the Date Opened cell on the New Accounts spreadsheet. I have attempted this function below and it has not worked. Not sure what I am doing wrong or if this is even possible.

=AND(‘New AccountsÂ’!$D2:$E2=$D2:$E2,$F2<Â'New AccountsÂ’!$A$2)

Ideally, this function would result in the Existing Accounts F2 and F3 being highlighted. Any help would be greatly appreciated.

Thanks,
oldfashionedgal


Spreadsheet 1 Title: New Accounts
A B C D E
Date Opened Account # Service Name ID #
10/17/17 111222 aaa Luc Leith 98765
10/17/17 111222 aaa Luc Leith 98765
10/17/17 111222 bbb Luc Leith 98765
10/17/17 222333 ccc Alex D 43210
10/17/17 333444 ddd Alex D 43210
10/17/17 555666 fff Jon Snow 65432


Spreadsheet 2 Title: Existing Accounts
A B C D E F
Date Opened Account # Service Name ID # Date Related
9/9/17 1234 aaa Jon Snow 65432 9/9/17
5/2/16 5678 bbb Luc Leith 98765 7/1/16
1/20/01 3456 ccc Queen Mary 76543 10/1/07

 

[inactiveuserps07]

@oldfashionedgal, Select F2:F??? (however far down your data goes) in the Existing Accounts sheet and apply the following conditional formatting formula:

=IFERROR(SUMPRODUCT(--(MATCH(D2,'New Accounts'!$D$2:$D$50000,0))*(MATCH(E2,'New Accounts'!$E$2:$E$50000,0))*(F2<'New Accounts'!$A$2:$A$50000))>0,FALSE)

Then [Format...] with the highlight color of your choice in the Fill tab.

If you have more or fewer than 50000 rows in the New Accounts sheet, adjust those three "50000" in the formula to be whatever you consider a "safe margin" that reflects the highest row count you'll ever expect to reach on the sheet.

 

[oldfashionedgal]

That worked beautifully! You have no idea how much you just made my day! Thank you so much, Erik Tyler! You are amazing!

 

[inactiveuserps07]

Hooray, @oldfashionedgal! Always glad to have a part in spreading good cheer (even if it's only through Excel formulas). :D