Conditional Format with vlookup

[lecxE]

I have two sets of data copied into a single worksheet which I'll call dataset1 and dataset2. I made a simple version below. In this example, dataset1 is A1:B4 and dataset2 is C1:D4. In column F, I used =VLOOKUP(D1,A$1:A$4,1,0) to identify which values in column D exist in column A.

A | B | C | D | E | F
AA |1 | |ZZ |2 | #N/A
BB |2 | |AA |1 |AA
CC |3 | |DD |2 |DD
DD |4 | |YY |4 | #N/A

Here's where I need help. First, instead of using column F, I'd prefer to use conditional formatting to identify which values in column D exist in column A, with a yellow fill.

Second, I like to used conditional formatting (an orange fill) to show which items are identical in both datasets. In the examle, only "AA | 1" meets this criteria.

Then end result would show D3 with a yellow fill since DD is in both datasets, and D3 would be orange because A1:B1 matched D2:E2.

Any help would be appreciated.

Thanks.

 

[martindwilson]

use as cf in d1 formula is =countif($a$1:$a$4,d1) copy format down

 

[lecxE]

Thanks, that did the trick for my first issue.

 

[martindwilson]

ok for identical applied to a1:b4
=ISNUMBER(MATCH($A1&$B1,INDEX($D$1:$D$4&$E$1:$E$4,0),0))
and applied to d1:e4
=ISNUMBER(MATCH($D1&$E1,INDEX($A$1:$A$4&$B$1:$B$4,0),0))

 

[lecxE]

Awesome, it worked perfectly! I understand the conditional formatting needs a true/false and that's what the formula does. However, I need to chew on this until I understand how it works. Thanks for the help!