Need Help with Array Formula using Vlookup and MAX

I have a problem that I cannot seem to get a solution to: I have a spreadsheet that details site visits to multiple sites for multiple individuals. Here is a sample:

Site # Investigator Visit Date
1001 Anderson, Joe 4/2/2008
1001 Anderson, Joe 6/3/2008
1001 Anderson, Joe 7/29/2008
1001 Anderson, Joe 9/25/2008
1001 Anderson, Joe 11/25/2008
1002 Donaldson, Dan 2/21/2008
1002 Donaldson, Dan 4/21/2008
1003 Thompson, Mike 1/14/2008
1003 Thompson, Mike 2/26/2008
1003 Thompson, Mike 4/16/2008
1003 Thompson, Mike 4/16/2008
1003 Thompson, Mike 5/28/2008
1003 Thompson, Mike 5/28/2008
1003 Thompson, Mike 7/16/2008
1003 Thompson, Mike 7/16/2008
1003 Thompson, Mike 8/28/2008
1003 Thompson, Mike 10/8/2008
1003 Thompson, Mike 12/3/2008



I need a formula that I can utilize that will 1) lookup the site number, and 2) return the most recent date to today's date. Ideally the formula would provide the answers in column B below:

Site # Most Recent Visit Date
1001 9/25/2008
1002 4/21/2008
1003 10/8/2008

Can you help?

if your data is arranged just like that with dates in increasing order
why not just use index match? with the match criteria =1 which will find he highest value =to i.e the last
with values in cols a,b,c,d
=INDEX(A1:D18,MATCH(E1,A1:A18,1),4) and 1003 entered in e1 returns
12/03/2008
1002>4/21/2008
and 1001>11/25/2008

Originally Posted by jllogan111

...

I need a formula that I can utilize that will 1) lookup the site number, and 2) return the most recent date to today's date. Ideally the formula would provide the answers in column B below:

Site # Most Recent Visit Date
1001 9/25/2008
1002 4/21/2008
1003 10/8/2008

Can you help?

Assuming E1:E3 is 1001, 1002 and 1003 respectively. Try this,

F1

Please Login or Register  to view this content.

Enter and copy down.
Hope this helps.