occurrence (last) reference

[excelFan]

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

 

[Biff]

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

 

[Ragdyer]

Just a non-array option:

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

 

[excelFan]

thanks Biff for your help
works ok

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

 

[excelFan]

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