Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup function returns duplicate values | Excel Discussion (Misc queries) | |||
vlookup returns #n/a | Excel Worksheet Functions | |||
VLOOKUP returns #REF!? | Excel Discussion (Misc queries) | |||
How do I write a VLOOKUP function that returns 0's, not neg vals? | Excel Worksheet Functions | |||
VLOOKUP Returns #REF | Excel Worksheet Functions |