A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Vlookup return most recent date (value)



 
 
Thread Tools Display Modes
  #1  
Old April 30th 10, 02:48 PM posted to microsoft.public.excel.misc
ryanholliday
external usenet poster
 
Posts: 3
Default 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
Ads
  #2  
Old April 30th 10, 02:55 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default 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  
Old April 30th 10, 02:58 PM posted to microsoft.public.excel.misc
Don Guillett[_2_]
external usenet poster
 
Posts: 1,517
Default 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  
Old April 30th 10, 03:03 PM posted to microsoft.public.excel.misc
Bernard Liengme[_2_]
external usenet poster
 
Posts: 563
Default 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


  #5  
Old April 30th 10, 03:06 PM posted to microsoft.public.excel.misc
Bernard Liengme[_2_]
external usenet poster
 
Posts: 563
Default 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  
Old April 30th 10, 03:34 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 5,942
Default 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  
Old April 30th 10, 03:40 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default 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  
Old April 30th 10, 04:04 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 5,942
Default 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  
Old April 30th 10, 04:09 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
lookup part number in a vertical list and return the most recent . James T[_2_] Excel Discussion (Misc queries) 4 August 28th 07 09:07 PM
formula to return date of most recent update Paul Excel Worksheet Functions 0 March 29th 07 02:10 AM
most recent date excelFan Excel Discussion (Misc queries) 1 December 21st 06 02:57 PM
VLOOKUP return text not date Paul Dennis Excel Worksheet Functions 3 September 28th 06 12:11 PM
Looking up the most recent date CatatonicBug Excel Worksheet Functions 3 September 8th 06 08:46 PM


All times are GMT +1. The time now is 02:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.