CODEWITHSUNDEEP
excelvlookupconditional-formatting
user3033933
user3033933

Reputation: 57

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

Conditional Formatting

Conditional Formatting 2

Upvotes: 1

Views: 2786

Answers (1)

TotsieMae
TotsieMae

Reputation: 835

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 Visual depiction of a helper column being added to Column A of a worksheet

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 Rules Manager showing Applies to range of =$A$2:$D$101

Conditional formatting applied to Sheet2 Visual depiction of conditional formatting result

After all of this, you should get your desired result.

Upvotes: 1

Related Questions