Conditional Formatting using vlookup

Question

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

What does an invalid RatePlanCde look like? Blank? Or with the word "invalid" or simply missing from the list? — drewster
Invalid RatePlanCde is random. It's not in any range. I know its invalid as the customers are not using it anymore and they have highlighted those in Sheet 1. There are 2500 of them that are invalid now. I need to find if they are in any other sheets and highlight those rows in other Sheets too. — user3033933
So is the highlighting the only indication that they are invalid? Or is there some other way to tell? — drewster
In my sheet, the only way to tell they are invalid is that they have been highlighted. I can copy these in a column and add "invalid" string to these. If I do that can it be used in conditional formatting? — user3033933
If you have a column that says 'invalid', yes, we can use that. There is no way to do anything based on the highlighting itself, however. — drewster

TotsieMae TotsieMae · Accepted Answer · 2017-09-29T04:04:46

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.

Conditional Formatting using vlookup

UPDATE:

1 Answers