Giving an excel file with 2 columns:

A: 5000 numbers (some repeated) B: 525 unique numbers

I want to know if for each number in A there is a instance of that number in B.

Meaning: for A1, is this value in column B?, if so highlight cell in green, otherwise, in RED.

I actually got the duplicates by using in column C this:


This gives me 0 as False and 1 as TRUE, so far so good!

Now what I want to know is how to build a conditional formatting .

  1. I go to Edit Formatting Rule
  2. Use a formula to determine wich cells to format
  3. RULE:


    A15 because cell A15 is true, A2 to A14 is false.

  4. Applies to: A2:A9999

However this highlights random cells.

So I think I did the hardest part but I am stuck in what seems to be the easiest.

Any guidance?

  • Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. – DavidPostill May 27 '15 at 18:36
  • use lookupv nested inside a IF – P. O. May 27 '15 at 18:36
  • Did you anchor the range references when you replicated the formula? – fixer1234 May 28 '15 at 20:47
  • Yes, I anchored the first part of the formula and I got the 0/1 (true/false) for all the cells, the issue is with the conditional formatting. I think I will try a workaround as I don´t even know if this is possible. – daviddgz May 28 '15 at 21:45
  • (1) Your question title is REALLY misleading.  It looks like you've solved the problem of finding duplicates, and your question is about conditional formatting.  (2) Have you tried making the conditional formatting formula the same as the one that works, with dollar signs in the same places?  (3) If that doesn't solve it, quantify "random".  Do they change when you refresh?  Can you reproduce the problem with 10 A values and 5 B values?  If so, post it.  Don't post screenshots, type in your data, as was done here and here. – Scott Jun 14 '15 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.