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

Answer: Vlookup return most recent date (value)
 

Hi Ryan,

To find the most recent timestamp for each ID using VLOOKUP, you can use a combination of the MAX and INDEX/MATCH functions. Here are the steps:

1. First, select your data range (including headers) and convert it into an Excel Table by pressing Ctrl+T or going to Insert Table. This will make it easier to reference the data in your formulas.
   
2. Next, create a new column to extract the ID number from Column A. You can use the LEFT function to do this. For example, if your first ID# is in cell A2, you can use the formula =LEFT(A2,3) to extract the first 3 characters (assuming all IDs are 3 characters long). Copy this formula down for all rows.
   
3. Now, create another column to find the most recent timestamp for each ID. You can use the MAX and INDEX/MATCH functions to do this. For example, if your ID numbers are in column C and timestamps are in column B, you can use the following formula in cell D2:
   
   
   Formula:

   ---

   =INDEX(B:B,MATCH(MAX(IF(C:C=C2,B:B)),B:B,0)) 


   ---

   This is an array formula, so you need to press Ctrl+Shift+Enter instead of just Enter to enter it. Then copy this formula down for all rows.
   
4. Finally, you can use VLOOKUP to retrieve the most recent timestamp for each ID. For example, if you have a list of unique IDs in another sheet (starting in cell A2), you can use the following formula in cell B2:
   
   
   Formula:

   ---

   =VLOOKUP(A2,Table1[[ID]:[Most Recent Timestamp]],2,FALSE) 


   ---

   This assumes that your Excel Table is named "Table1" and the columns with ID numbers and most recent timestamps are named "ID" and "Most Recent Timestamp", respectively. Copy this formula down for all rows.
   
   That's it! This should give you the most recent timestamp for each ID using VLOOKUP. Let me know if you have any questions or need further assistance.
   

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

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

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
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))
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

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

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

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

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

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