Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In my Excel document, I have one sheet containing a column of important dates. In another sheet I have a list of all dates. I want to conditionally format that list such that if the date is present in the important dates column, it is highlighted.

What function should I use to determine if a value is present in a range of values? The LOOKUP, VLOOKUP, and HLOOKUP functions do not appear to do what I want.

share|improve this question
Are you 100% you used all syntax options of LOOKUP and it's variants? Because what I typed in my answer is probably a "dumb" solution compared to using those functions – Ivo Flipse Aug 14 '09 at 11:42
up vote 2 down vote accepted

Apply the following conditional Formatting rule formula to your list of all dates:


Where "D1" is the cell-reference of the first cell in the all-dates range, and "A$1:A$5" is the important-dates range (be sure to add the Workbook and Sheet references),

This assumes vertical lists of course.

share|improve this answer

It might be your problem is not in the looking up, but in the conditional formatting:

One important thing to bear in mind with conditional formatting is that criteria are evaluated in the order in which they appear. Once a criteria has been met, then the formatting is applied and other criteria are not tested. It is therefore important to set out the tests in the correct order. If, in the example above, the criteria had been entered in the reverse order, i.e. test for 14 days, then 7 and then 0, it would have only applied the 14 days format even if the date entered was today. In other words, if the date is today then all three of the tests would have been met so you have to be careful of the order in order to get the result you need

And as an alternative to LOOKUP are you looking for something like this?

Date Intervals

This page describes a few methods for working with intervals of dates.
Specifically, it address the questions of whether a date falls within an interval, the number of days that two intervals overlap, and the how many days are in one interval, excluding those days in another interval. These formulas can be quite useful for scheduling applications, such as employee vacation schedules.

Is A Date In An Interval?

Suppose we have 3 dates -- a start date, and end date, and a test date.
We can test whether the test date falls within the interval between start date and end date. In this formula, we will use three named cells: TDate1 for the start date, TDate2 for the end date, and TDate for the test date. This formula will return either TRUE or FALSE, indicating whether TDate falls in the interval.


For example if TDate1 is 1-Jan and TDate2 is 31-Jan , and TDate is 15-Jan , the formula will return TRUE, indicating that TDate falls in the interval.

In this formula, it does not matter whether TDate1 is earlier or later than TDate2.

Note: the page contains some more information on what to do, but I have a feeling you want something more dynamic.

share|improve this answer

Sorry for the images as an answer, but it's faster for me:

enter image description here

enter image description here

share|improve this answer
And apparently, I can't tell when a question is nearly 3 months old. – PileOfMush Nov 5 '09 at 17:40

There are good free Calender Templates available at David Seah's site.
This has ZIP packs with XLS templates that use conditional formatting like you desire.

You might even consider using the templates directly...

Formatting sample,


D12 is the cell location on the formatted sheet.
ISNA checks if the VLOOKUP failed to locate the date.
The formatting is applied if match is confirmed (with a double negative check).

The important dates here are obviously the Holidays! :-)

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .