Sorry, a small typo

=IF(ISNA(MATCH("H",IF(($1:$1=TODAY())*(2:2="H"),2 :2),0)),"No planned
holiday",INDEX($1:$1,MATCH("H",IF((1:1=TODAY())*( 2:2="H"),2:2),0)))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Andy,

You can't use LOOKUP this way as it finds the highest instance less that
the
search value after TODAY(), whereas you want the first instance after
TODAY().

It can be done though ( and I did work it out last time, just messed up
the
cut and paste :-(), with

=IF(ISNA(MATCH("H",IF(($1:$1=TODAY())*(2:2="H"),2 :2),1)),"No planned
holiday",INDEX($1:$1,MATCH("H",IF((1:1=TODAY())*( 2:2="H"),2:2),0)))

again an array formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"farutherford"
wrote in message
news:farutherford.1uf3qb_1125137113.8977@excelforu m-nospam.com...

Thanks Bob. Wicked stuff. I hope you don't mind me asking for another
point of clarification so here goes.

For the Previous MC and Next Holiday formulae, they are the same in
your message so I changed the Next Holiday to read:

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1=TODAY())* (2:2="h"),2:2),1)),"No
planned


holiday",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1 =TODAY())*(2:2="h"),2:2),1
)))

The problem is that it does find the next holiday but since its
=TODAY, it returns the last day of the next holiday. I need it to
return the first day. If you have a different formula for this and
could post it, that would be great. Time for me to pick up a good book
on Excel covering formulae and arrays.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile:
http://www.excelforum.com/member.php...o&userid=26663
View this thread:
http://www.excelforum.com/showthread...hreadid=399389