Printable View
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.
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))
This is an ARRAY formula that must be entered/edited using ctrl+shift+enter =MIN(IF(a6:a66="a",B6:B66))
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 5-digit 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)).
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. You can try =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>.
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.