Conditional Formatting with VLOOKUP

Posted on 2014-08-22
Last Modified: 2014-08-25
I am using VLookup to pull values in from one Excel tab to another. I want to make the cell change color if the user manually types in a value to replace the VLOOKUP value. So, for example, if the user goes into Cell B2 on the tab with the VLOOKUP value of "Joe" and types in "Jack" instead, I want the cell to change color so the user can see what they have changed.

I've tried this as a conditional formatting test: ="VLOOKUP(A2,SEG_D1!A:B,2,FALSE)<>B2"

The first part yields the original value and is the formula in Cell B2. If the user types over this value and puts something else in there than what the VLOOKUP yields, I want the cell to change color. Looks to me like the above would apply the test between what the VLOOKUP is yielding and what is actually in Cell B2. But when I change the value in B2 manually, it doesn't change color.

Any suggestions appreciated.

Question by:Buck_Beasom
    LVL 20

    Expert Comment

    by:Ejgil Hedegaard
    You can't reference another sheet in a conditional format formula.

    But with a named range it is possible.
    Name columns A:B on the sheet SEG_D1 LookupRange
    Then use the name in the Vlookup formula like this =VLOOKUP(A2,LookupRange,2,FALSE)<>B2

    Author Comment

    Seems like it should work. I named the range "RPTID" in the SEG_D1 tab and the VLOOKUP works, but when I put this into the conditional format:


    It still doesn't change color when I change the B2 value.

    LVL 20

    Accepted Solution

    You must remove the 2 " in the formula.
    ="VLOOKUP(A2,RPTID,2,FALSE)<>B2" means equal to the text.
    =VLOOKUP(A2,RPTID,2,FALSE)<>B2 is a formula.

    Author Closing Comment

    Awesome! That's got it! THANKS!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
    Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
    This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now