Pode ser que o seu problema não esteja na pesquisa, mas na formatação condicional :
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
E como alternativa ao LOOKUP, você está procurando por algo assim?
Intervalos de datas
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.
=AND((TDate>=MIN(TDate1,TDate2)),TDate<=MAX(TDate1,TDate2))
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.
Nota: a página contém mais algumas informações sobre o que fazer, mas tenho a sensação de que você quer algo mais dinâmico.