0

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

Improve this question
6
  • Don't use &. Use the AND function to combine both of your VLOOKUP statements. It looks like it would be =if(AND(VLOOKUP(A2,$C2:$C93,1,FALSE)>0,VLOOKUP(B2,$D2:$D93,1,FALSE)),True,False)>0 Commented Jun 21, 2018 at 14:27
  • Thanks..There was a typo- =if(AND(VLOOKUP(A2,$C2:$C93,1,FALSE)>0,VLOOKUP(B2,$D2:$D93,1,FALSE)>0),True,False)>0 Commented Jun 21, 2018 at 14:41
  • Unfortunately, this too is not working as expected. Seems like it is only highlighting when (Value in cell A = Value in C )And (Value in B = Value in D). It is not doing a vertical lookup. Commented Jun 21, 2018 at 15:09
  • So you want to highlight row 2 if the value in cell A2 occurs anywhere in column C and the value of B2 occurs anywhere in column D? Commented Jun 21, 2018 at 16:02
  • Yes. That's exactly what I am trying to do. A2 occurs anywhere in column C and the value of B2 occurs anywhere in column D. But as shown in the print screen above in question- the catch here is some of the values in column B are Null. I am trying to find A2 (CustEID) anywhere in column C and then do a lookup for B2 (AccountEID) anywhere in column D. Account EIDs maybe NULL. Commented Jun 21, 2018 at 16:17

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.