Excel: if duplicate cell value found in another column then highlight green

Question

Can someone help me, I'm not sure what formula to use.

I have highlighted the cell in the picture to show an example of what I mean.

What I want to do is highlight the cell in column A where the value matches a value in column D, it does not have to be in the same row. If there is a duplicate value in column D then highlight the cell in column A green.

Thanks for any help given.

Solution

I'm not too good in conditional formatting, but it seems that you will have to use a formula which can lookup the values of column D in column A. A simple one is MATCH.

=MATCH(A1,D:D,0)

The 0 means that exact match, you'll most of the time prefer this over the other options.

Put that in the formula box and pick your formatting.

Make sure you have selected the whole column A before applying the conditional formatting.