Excel Conditional Formatting: If TableColumn = value1 AND ValueFromSameRowButAnotherColumn = value2

Question

Context:

I'm working off of a template calendar that autofills a calendar if events and times are listed in a table in another tab.

From that table, I've named the date column: Dates; I've named the event column: Event

In the calendar, each date has 4 rows 1 column with the date in the first column (e.g. Row B).

Problem:

On the calendar, I want to conditionally format so that if the STAT holiday date si listed in the events table, then the 4 rows in the date is shaded grey.

I tried the following formulas but none of them work: =(IF then conditional format)

• =AND((Event="STAT"),(Dates=A$8))
• =(Event="STAT")*(Dates=B$4) --> Another question said the * worked the same as AND for conditional formatting

Basically, if the value in the 'Date Column' is the date in the calendar grid, AND if the Event, in the same row as the date referenced in the 'Date Column', is "STAT", then shade the cell grey.

Hope it was all clear, thank you in advance :)

Answer 1

You can't use conditional format to set the value of a cell so in the left cell on your calendar you'll have to put a formula somthing like this, assuming your events table is called EventsTable and is in Sheet1 (without sample data it's tricky):

`=IF(ISERROR(VLOOKUP(DateYouWantToLookUp, Sheet1!EventsTable, 2)), "", VLOOKUP(DateYouWantToLookUp, Sheet1!EventsTable, 2))

And for the conditional formatting, assuming you calendar starts in A1, you'll need to use the following formula:

=$D22="STAT" and for the 'Applies to' area, you'll need to use =$A$1:$D$XX where XX=last row in you calendar.