occurrence (last) reference
 

hi all,
please help
A1:A100 is for the date values (for each entry a single date)
B1:B100 is holding names (text values) which might be repeated in many
entries
C1 is a referece cell, where i may put any name (from the list B1:B100)
where i want to retrieve the adjacent date of the last occurrence of that
particular name which i put in C1
many thanks for any help

Hi!

Try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=MAX(IF(B1:B100=C1,A1:A100))

Format the cell as DATE

Biff

-----Original Message-----
hi all,
please help
A1:A100 is for the date values (for each entry a single
date)
B1:B100 is holding names (text values) which might be
repeated in many
entries
C1 is a referece cell, where i may put any name (from
the list B1:B100)
where i want to retrieve the adjacent date of the last
occurrence of that
particular name which i put in C1
many thanks for any help
.

Just a non-array option:

=SUMPRODUCT(MAX((B1:B100=C1)*A1:A100))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
Hi!

Try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=MAX(IF(B1:B100=C1,A1:A100))

Format the cell as DATE

Biff

-----Original Message-----
hi all,
please help
A1:A100 is for the date values (for each entry a single
date)
B1:B100 is holding names (text values) which might be
repeated in many
entries
C1 is a referece cell, where i may put any name (from
the list B1:B100)
where i want to retrieve the adjacent date of the last
occurrence of that
particular name which i put in C1
many thanks for any help
.

thanks Biff for your help
works ok

"Biff" wrote:

Hi!

Try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=MAX(IF(B1:B100=C1,A1:A100))

Format the cell as DATE

Biff

-----Original Message-----
hi all,
please help
A1:A100 is for the date values (for each entry a single
date)
B1:B100 is holding names (text values) which might be
repeated in many
entries
C1 is a referece cell, where i may put any name (from
the list B1:B100)
where i want to retrieve the adjacent date of the last
occurrence of that
particular name which i put in C1
many thanks for any help
.

many thanks Ragdter
your suggestion is excellent and was very helpful to me

"Ragdyer" wrote:

Just a non-array option:

=SUMPRODUCT(MAX((B1:B100=C1)*A1:A100))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
Hi!

Try this array formula entered with the key combo of
CTRL,SHIFT,ENTER:

=MAX(IF(B1:B100=C1,A1:A100))

Format the cell as DATE

Biff

-----Original Message-----
hi all,
please help
A1:A100 is for the date values (for each entry a single
date)
B1:B100 is holding names (text values) which might be
repeated in many
entries
C1 is a referece cell, where i may put any name (from
the list B1:B100)
where i want to retrieve the adjacent date of the last
occurrence of that
particular name which i put in C1
many thanks for any help
.