CODEWITHSUNDEEP
excelconditional-formatting
CJ0206
CJ0206

Reputation: 95

Excel Conditional Formatting - Multiple Conditions

I am trying to put some conditional formatting into a spreadsheet but I have become stuck, I have three things I need to check:

  1. Does the name in column A match with one in D
  2. Is the date in column E today or earlier
  3. Is the date in column G today or later

If all of these conditions are met then the corresponding cell in column A will change colour.

Here's whats in the table:

So far I can only get it to highlight if the names match, I'm struggling to add the other conditions even after Googling for hours:

=NOT(ISNA(VLOOKUP($A1,$D:$D,1,FALSE)))

I also tried this, to no effect:

=AND($A2=$D:$D,$E1<TODAY(),$G1>TODAY())

Any suggestions?

Upvotes: 1

Views: 296

Answers (2)

Gravitate
Gravitate

Reputation: 3064

@jsheeran's answer will work. However, if you apply it to many cells, it could become slow due to the VLOOKUP. INDEX/MATCH is a one to one replacement for VLOOKUP and it is faster, more adaptable, and doesn't break if you were to insert a column between D and E.

=AND(
    IFERROR(
        INDEX($E:$E, MATCH($A1,$D:$D,0))<=TODAY(), 
        FALSE
    ), 
    IFERROR(
        INDEX($G:$G, MATCH($A1,$D:$D,0))>=TODAY(), 
        FALSE
    )
)

Upvotes: 1

jsheeran
jsheeran

Reputation: 3037

You're close. I tried the following formula and it appears to work:

=AND(IFERROR(VLOOKUP($A1,$D:$E,2,FALSE)<TODAY(),FALSE),IFERROR(VLOOKUP($A1,$D:$G,4,FALSE)>TODAY(),FALSE))

The result of this is:

enter image description here

Upvotes: 1

Related Questions