Find last occurance of text in range

I need to find the last occurance of text in a range of cells. The range can have empty cells and various text values. This is a project staff schedule and I want to have a formula that will display the Last Sick Day and Next Vacation Day.

Column A, Row 2 contains the consultant name
Row 1 contains the date (each day from project start to project end)
Intersecting sells contain "MC" for a sick day and "H" for a vacation day or blank for neither.

In laymans terms:
For Last Sick Day I want to look at today's date, search across Row 5 to find that date, go backwards in time (columns) to find the cell reference for the last occurance of "MC" and return the date value from Row 5.

For Next Vacation Day I want to look at today's date, search across Row 5 to find that date, go forward in time (colums) to find the cell reference for the next occurance of "H" and return the date value from Row 5.

I've tried FIND, MATCH (returns the first occurance and doesn't seem to work across empty cells) and LOOKUP. My guess is that this will involve a HLOOKUP and possibly MAX but I am lost.

Any help, pointers or a nice cold beer are appreciated! My project team is large and we want to make sure we control their movements well.

Cheers,
Andy

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Thanks a ton Bob. The forumla worked great and now I understand arrays just a little bit better.

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

Andy,

Last sickness

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Next holiday

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1<=TODAY())*(2:2="MC"),2:2),1)),"No
previous
sickness",INDEX($1:$1,MATCH("ZZZZZZZZZZZZ",IF((1:1<=TODAY())*(2:2="MC"),2:2)
,1)))

Both are array formulae, so commit with Ctrl-Shift-End

--

HTH

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


"farutherford" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to find the last occurance of text in a range of cells. The
> range can have empty cells and various text values. This is a project
> staff schedule and I want to have a formula that will display the Last
> Sick Day and Next Vacation Day.
>
> Column A, Row 2 contains the consultant name
> Row 1 contains the date (each day from project start to project end)
> Intersecting sells contain "MC" for a sick day and "H" for a vacation
> day or blank for neither.
>
> In laymans terms:
> For Last Sick Day I want to look at today's date, search across Row 5
> to find that date, go backwards in time (columns) to find the cell
> reference for the last occurance of "MC" and return the date value from
> Row 5.
>
> For Next Vacation Day I want to look at today's date, search across Row
> 5 to find that date, go forward in time (colums) to find the cell
> reference for the next occurance of "H" and return the date value from
> Row 5.
>
> I've tried FIND, MATCH (returns the first occurance and doesn't seem to
> work across empty cells) and LOOKUP. My guess is that this will involve
> a HLOOKUP and possibly MAX but I am lost.
>
> Any help, pointers or a nice cold beer are appreciated! My project
> team is large and we want to make sure we control their movements
> well.
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>