Conditional format cells based on match in another sheet I have 2 excel sheets (Using Microsoft excel 2011 for mac)

sheet1(Column D)
foo
bar

sheet2
abc cde foo
sfd sdf dgf
bar


I have applied 3 rules using conditional format: 1. If any of the data in Sheet2 matches with Sheet1(ColumnD) - Make text as blue 2. If there are any duplicate values in sheet2 - Make text as yellow 3. If the above 2 are not there - leave it blank

But Only Rule2 and Rule3 are working. How can I make all of them work.And I how can I add one more rule which returns values which satisfies both Rule1 and Rule2

Rules:

Rule 1 # =NOT(ISERROR(MATCH($1:$1048576,'Sheet1'!$D:$D,0)))
Rule 2 # Highlight Duplicate values with yellow text
Rule 3 # =ISBLANK($1:$1048576)
I'm not sure I am parsing the problem correctly, but I suspect this is the solution.

Make a new column in one of the pages that has a formula like...

=IF(thispage!D2=otherpage!D2,"*","")

This will make a column where all the duplicates have a *. Now simply make a conditional on the other column that turns the text yellow if there is a * in there.