text: Q:
  
  Conditional Formatting using vlookup
  
  I have Sheet 1 as
  AssocNum      RatePlanCde    RateType     RateTypeDesc
       272       911               2         Adjustable
       472       1115              2         Fixed
  
  Many of these RatePlanCde are not valid anymore. These have been highlighted in red in Sheet 1. For example, 911 is not a valid RatePlanCde, while RatePlanCde 1115 is still valid.
  I have sheet 2 as
  AssocNumEID     RateCostComponentCde    RatePlanCde         ReadOnlyInd
  272               2                      911                         Null
  472               4                      1                           Null  
  
  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 VLOOKUP will be appreciated.
  
  UPDATE:
  Updating to add more details.
  I tried the COUNTIFS solution proposed here but for some reason it is not working for me. What am I missing?
  I have added a helper column to Column B of Sheet1 (aka RatePlan) that includes either Valid or Invalid for the corresponding RatePlanCde column (Column C).
  I am using this formula under conditional formatting in Sheet 2 for conditional formatting:
  =COUNTIFS(RatePlan!$B$2:$B$2514,"Invalid",RatePlan!$C$2:$C$2514,$C2)>0
  
  A:
  
  I realize your answer was looking for a solution using VLOOKUP, but I think a cleaner method would be to use a COUNTIFS formula.
  As mentioned by @drewster, you'll first need to add a helper column to Sheet1. I placed mine in Sheet1!Column A so that it's out of the way of the user and less likely to be corrupted in any way, especially after hiding it, but you can place yours in any convenient column.
  There is no formula in Sheet1!Column A, only hardcoded values (which you'll need to enter).
  Helper column added to Sheet1
  
  Then on Sheet2 you need to add a conditional formatting rule using the Use a formula to determine which cells to format rule. In the formula box under where it says, Edit the Rule Description, your formula will be:
  =COUNTIFS(Sheet1!$A$2:$A$100,"Invalid",Sheet1!$C$2:$C$100,$C2)>0
  The ranges I used in the formula - Sheet1!$A$2:$A$100 & Sheet1!$C$2:$C$100 - should cover your entire "table" of information on Sheet1, so adjust those as needed. $C2 is the first cell on the conditionally formatted sheet that you are attempting to match to Sheet1!$C$2:$C$100. 
  Also, make sure your rule Applies to your entire "table" of information on Sheet2 (my example uses range =$A$2:$D$101, so feel free to change that as well).
  Conditional Formatting Rules Manager for Sheet2
  
  Conditional formatting applied to Sheet2
  
  After all of this, you should get your desired result.
  
  