Excel Conditional Formatting - Multiple Conditions

1
votes

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:

  • There is a list of staff in column A
  • If they are out of office at any time during the week their name will be in column D
  • Column E is the start of their time off
  • Column G is the end of their time off

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?

2
excelconditional-formatting

2 Answers

1
votes

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

1
votes

@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
    )
)