Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 1 down vote accepted

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.


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.

share|improve this answer
thank you very much! – gene May 15 '13 at 23:47

If you use countif, you can find the duplicates instead (match will only find the first occurrence of the value):


You can use =1 to find values that occur only once and >0 to find values that occur any number of times

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.