CODEWITHSUNDEEP
excelvlookupconditional-formatting
user3033933
user3033933

Reputation: 57

Conditional Formatting using multiple vlookups

I have an excel spreadsheet and I am trying to do conditional formatting based on multiple conditions. I have to highlight the rows where (Column A value matches column C) AND (Column B matches column D). I tried 3 ways but none of them are giving me expected results- Method 1 - I tried conditional formatting with these 2 Rules- (VLOOKUP($A2,C2:C93,1,FALSE))>0 (VLOOKUP($B2,D2:D93,1,FALSE))>0 and applied it to $A$2:$D$5745 but this is not working as expected.

Method 2- I tried using if but this is also not providing me desired results =if(VLOOKUP(A2,$C2:$C93,1,FALSE)>0 & VLOOKUP(B2,$D2:$D93,1,FALSE),True,False)>0 applied it to $A$2:$D$5745

Method 3- =AND((VLOOKUP($A2,C2:C93,1,FALSE))>0,(VLOOKUP($B2,D2:D93,1,FALSE))>0) applied it to $A$2:$D$5745

To rephrase this problem- I would like to highlight all rows where CustEID in Col A and Account EID in cloumn B match CustEID in col C and Account EID in col D.

Can someone please guide me?

Sample Data

Upvotes: 0

Views: 1280

Answers (1)

PeterT
PeterT

Reputation: 8557

Here's what I was able to get working.

  1. The VLOOKUP evaluates to return either the "found" value or #N/A. By modifying your formula with a logical check >0, this converts the result to a boolean value (TRUE) but only in the case where VLOOKUP is returning a valid value. In many of your cases, your formula still evaluates to #N/A.

So this: =VLOOKUP(A2,$C$2:$C$93,1,FALSE)>0 will return either TRUE or #N/A.

I've modified the formula to =IFNA(VLOOKUP(A2,$C$2:$C$93,1,FALSE)>0,FALSE), which forces the entire formula to return a true boolean value TRUE or FALSE.

  1. The cell range references in your formulas need to be locked into specific ranges that will not be evaluated as "relative" in the context of the conditional format formula. So your formula VLOOKUP($A2,C2:C93,1,FALSE) using the range C2:C93 will also "slide" (my own terminology for this formula going "relative") as it progresses down the rows. So each of your formulas needs to lock this down with VLOOKUP($A2,$C$2:$C$93,1,FALSE).

Notice that the only portion of the formula that stays relative is the row number -- the 2 in this case. So you'll start your conditional format setup on row 2.

  1. Combining these formulas for the full test you want to apply gets you

=AND(IFNA(VLOOKUP($A2,$C$2:$C$93,1,FALSE)>0,FALSE),IFNA(VLOOKUP($B2,$D$2:$D$93,1,FALSE)>0,FALSE))

  1. Applying this to your conditional format as a full row requires one last adjustment. Instead of applying your rule to the range $A$2:$D$5745, you have to remove the column references. So the application range becomes $2:$5745.

This is what I get when it's all put together:

enter image description here

Upvotes: 1

Related Questions