# Conditional format date if it matches two conditions

#### cpereznj

##### New Member
I have searched the web for a solution to my question with no luck. Here is my issue:
I have cell A1 with a date (any date), I also have two named ranges. One named Holidays and the other one named Payroll. They are used to identify the company's holidays and the payroll dates.
I need to conditional format cell A1 if the date matches a date in the Holidays range and a date in the Payroll range. Tried unsuccessfully the following formula: =AND(A1=Holidays,A1=Payroll). I have used =OR(A1=Holidays) plus =OR(A1=Payroll). Both work properly. However, I need it to work if both conditions are met not separately. Any help will be appreciated.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Fluff

##### MrExcel MVP, Moderator
Try
=AND(OR(A1=Holidays),OR(A1=payroll))

#### jjasmith4

##### New Member
Holidays and Payroll have more than one date, right? So just A1=Holidays or A1=Payroll fail.

Try this:
=NOT(ISNA(MATCH(A1,Holidays,0)+MATCH(A1,Payroll,0)))

#### cpereznj

##### New Member
Thank you both. The two formulas worked correctly.

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

Replies
2
Views
51
Replies
1
Views
36
Replies
1
Views
42
Replies
0
Views
65
Replies
7
Views
171

1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom