vlookup function that returns the greates/oldest of many to one

[confused!!]

Column A is a list of user names that repeat many times. Column B is a list
dates corresponding to the names that repeat in column A. Column D is a list
of Active user names that do not repeat.

I need to look up the Active user name in Column D from Column A in order to
return the greatest/most recent date from column B.

Example of existing data:
User Name Date Active User Name
JohnDoe 03/20/09 JohnDoe
JohnDoe 02/20/09 JaneDoe
JohnDoe 01/20/09 MargeLoe
JohnDoe 12/20/08 DonRoe
JohnDoe 11/20/09 PhilWoe

Example of search results:
User Name Date Active User Name
JohnDoe 03/20/09 JohnDoe

 

[T. Valko]

It appears that you want the max date that is less than or equal to today's
date otherwise the greatest/most recent date for JohnDoe is 11/20/2009.

Try this array formula** :

=MAX(IF((A2:A6=C2)*(B2:B6<=TODAY()),B2:B6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

 

[confused!!]

Great!!!!! it works!!!!

HOWEVER i tred to copy paste function on row 3 the formula didn't search
back to row 2 for information.

In order to search on row 2 i had to edit the formula and change all the 3s
to 2s.

Edit/paste special doesn't work. Any suggestions?

 

[T. Valko]

Make the row references absolute:

=MAX(IF((A$2:A$6=C2)*(B$2:B$6<=TODAY()),B$2:B$6))

Then you can just drag copy down the column.