Home 
Search 
Today's Posts 
#1




Vlookup return most recent date (value)
Hello,
I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#2




Vlookup return most recent date (value)
With your data in ColA/B and the query ID in cell C1 try the below
=SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))  Jacob (MVP  Excel) "ryanholliday" wrote: Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#3




Vlookup return most recent date (value)
This is an ARRAY formula that must be entered/edited using ctrl+shift+enter
=MIN(IF(a6:a66="a",B6:B66))  Don Guillett Microsoft MVP Excel SalesAid Software "ryanholliday" wrote in message ... Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#4




Vlookup return most recent date (value)
Starting in D1 I have a column of unique IDs  made with Advanced Filter
In E1 I used this array formula =MAX(IF(A:A=D1,B:B)) (Array formula  so complete it with CTR+SHIFT+ENTER not just ENTER) You will need to format the cell as Date otherwise it will display the 5digit serial number of the date Copy down the column If you use ranges, make use you use absolute references as in =MAX(IF($A$1:$A$100=D1,$B$100:$B1$100)) best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "ryanholliday" wrote in message ... Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#5




Vlookup return most recent date (value)
Don:
MIN or MAX if he wants the most recent? Bernard "Don Guillett" wrote in message ... This is an ARRAY formula that must be entered/edited using ctrl+shift+enter =MIN(IF(a6:a66="a",B6:B66))  Don Guillett Microsoft MVP Excel SalesAid Software "ryanholliday" wrote in message ... Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#6




Vlookup return most recent date (value)
I could not get that to work... In it's current form it just returned zero. I
tried =SUMPRODUCT(MAX(($A$2:$A$100=C1), $B$2:$B$100)) But that just returned the max date regardless of the ID selected. Here is what I ended up with... =MAX(IF($A$2:$A$100=C1, $B$2:$B$100)) ***Note this is an array formula and MUST be committed using Shift + Ctrl + <Enter  HTH... Jim Thomlinson "Jacob Skaria" wrote: With your data in ColA/B and the query ID in cell C1 try the below =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))  Jacob (MVP  Excel) "ryanholliday" wrote: Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#7




Vlookup return most recent date (value)
Jim, could you please try again..with the alternate query IDs in cell c1...
Col A Col B Col C 1 7:21 PM 3 1 7:22 PM 2 8:05 PM 2 8:06 PM 3 9:06 PM 3 9:07 PM  Jacob (MVP  Excel) "Jim Thomlinson" wrote: I could not get that to work... In it's current form it just returned zero. I tried =SUMPRODUCT(MAX(($A$2:$A$100=C1), $B$2:$B$100)) But that just returned the max date regardless of the ID selected. Here is what I ended up with... =MAX(IF($A$2:$A$100=C1, $B$2:$B$100)) ***Note this is an array formula and MUST be committed using Shift + Ctrl + <Enter  HTH... Jim Thomlinson "Jacob Skaria" wrote: With your data in ColA/B and the query ID in cell C1 try the below =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))  Jacob (MVP  Excel) "ryanholliday" wrote: Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#8




Vlookup return most recent date (value)
You are correct. I had an issue with Text vs Numbers...
 HTH... Jim Thomlinson "Jacob Skaria" wrote: Jim, could you please try again..with the alternate query IDs in cell c1... Col A Col B Col C 1 7:21 PM 3 1 7:22 PM 2 8:05 PM 2 8:06 PM 3 9:06 PM 3 9:07 PM  Jacob (MVP  Excel) "Jim Thomlinson" wrote: I could not get that to work... In it's current form it just returned zero. I tried =SUMPRODUCT(MAX(($A$2:$A$100=C1), $B$2:$B$100)) But that just returned the max date regardless of the ID selected. Here is what I ended up with... =MAX(IF($A$2:$A$100=C1, $B$2:$B$100)) ***Note this is an array formula and MUST be committed using Shift + Ctrl + <Enter  HTH... Jim Thomlinson "Jacob Skaria" wrote: With your data in ColA/B and the query ID in cell C1 try the below =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))  Jacob (MVP  Excel) "ryanholliday" wrote: Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
#9




Vlookup return most recent date (value)
OK. I remember a similar response from you few months back while I used
SUMPRODUCT() MAX() combination; but I found that too late to respond...It works when the criteria (here ColA) is both text and numerics...Thanks Jim  Jacob (MVP  Excel) "Jim Thomlinson" wrote: You are correct. I had an issue with Text vs Numbers...  HTH... Jim Thomlinson "Jacob Skaria" wrote: Jim, could you please try again..with the alternate query IDs in cell c1... Col A Col B Col C 1 7:21 PM 3 1 7:22 PM 2 8:05 PM 2 8:06 PM 3 9:06 PM 3 9:07 PM  Jacob (MVP  Excel) "Jim Thomlinson" wrote: I could not get that to work... In it's current form it just returned zero. I tried =SUMPRODUCT(MAX(($A$2:$A$100=C1), $B$2:$B$100)) But that just returned the max date regardless of the ID selected. Here is what I ended up with... =MAX(IF($A$2:$A$100=C1, $B$2:$B$100)) ***Note this is an array formula and MUST be committed using Shift + Ctrl + <Enter  HTH... Jim Thomlinson "Jacob Skaria" wrote: With your data in ColA/B and the query ID in cell C1 try the below =SUMPRODUCT(MAX(($A$2:$A$100=C1)*$B$2:$B$100))  Jacob (MVP  Excel) "ryanholliday" wrote: Hello, I have a set of data where Column A contains an personal ID# (001). Column B contains a timestamp. My table has multiple entries for each ID# and different timestamps for each entry. I want to use a vlookup to find the MOST RECENT timestamp for EACH ID. Any help would be greatly appreciated. Thanks, Ryan 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
lookup part number in a vertical list and return the most recent .  Excel Discussion (Misc queries)  
formula to return date of most recent update  Excel Worksheet Functions  
most recent date  Excel Discussion (Misc queries)  
VLOOKUP return text not date  Excel Worksheet Functions  
Looking up the most recent date  Excel Worksheet Functions 