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

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

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

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

--
Biff
Microsoft Excel MVP


"confused!!" wrote in message
...
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

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

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" wrote:

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

--
Biff
Microsoft Excel MVP


"confused!!" wrote in message
...
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

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

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.

--
Biff
Microsoft Excel MVP


"confused!!" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"confused!!" wrote in message
...
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