Find last occurance of text in range

[farutherford]

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

 

[Bob Phillips]

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]...

 

[farutherford]

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

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

=IF(ISNA(MATCH("ZZZZZZZZZZZZ",IF(($1:$1>=TODAY())*(2:2="h"),2:2),1)),"N
planne
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 it

=TODAY, it returns the last day of the next holiday. I need it t

return the first day. If you have a different formula for this an
could post it, that would be great. Time for me to pick up a good boo
on Excel covering formulae and arrays.

Cheers,
And

 

[Bob Phillips]

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

 

[Bob Phillips]

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)

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

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

 

[farutherford]

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