title: Conditional Formatting using vlookup
body: <p>I have Sheet 1 as</p>
  <pre><code>AssocNum      RatePlanCde    RateType     RateTypeDesc
       272       911               2         Adjustable
       472       1115              2         Fixed
  </code></pre>
  <p>Many of these <strong>RatePlanCde</strong> are not valid anymore. These have been highlighted in red in Sheet 1. For example, <em>911</em> is not a valid <strong>RatePlanCde</strong>, while <strong>RatePlanCde</strong> <em>1115</em> is still valid.</p>
  <p>I have sheet 2 as</p>
  <pre><code>AssocNumEID     RateCostComponentCde    RatePlanCde         ReadOnlyInd
  272               2                      911                         Null
  472               4                      1                           Null  
  </code></pre>
  <p>I need to find the rows in Sheet 2 where the RatePlanCde is invalid and highlight those rows. Any help in doing this using conditional formatting with <code>VLOOKUP</code> will be appreciated.</p>
  <br/>
  <h2>UPDATE:</h2>
  <p>Updating to add more details.
  I tried the <code>COUNTIFS</code> solution proposed here but for some reason it is not working for me. What am I missing?
  I have added a helper column to <strong>Column B</strong> of Sheet1 (aka <strong>RatePlan</strong>) that includes either <em>Valid</em> or <em>Invalid</em> for the corresponding <strong>RatePlanCde</strong> column (<strong>Column C</strong>).</p>
  <p>I am using this formula under conditional formatting in Sheet 2 for conditional formatting:</p>
  <p><code>=COUNTIFS(RatePlan!$B$2:$B$2514,&quot;Invalid&quot;,RatePlan!$C$2:$C$2514,$C2)&gt;0</code></p>
  <p><a href="https://i.stack.imgur.com/f2DXX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f2DXX.png" alt="Conditional Formatting" /></a></p>
  <p><a href="https://i.stack.imgur.com/xs3sr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xs3sr.png" alt="Conditional Formatting 2" /></a></p>