Looking up a maximum value that matches criteria. VLOOKUP?
 

Hi,
I'm trying to do a VLOOKUP, but I need the maximum value of a
particular column. For example, in the data below:

Col A Col B Col C
Name Start End
Andy 01 January 1997 03 February 2005
Belinda 02 April 1996 08 January 2000
Charlie 03 February 2001 10 October 2002
Andy 01 January 1997 01 March 2004

In column D I want the maximum End date for each person. So, for the
above:
Col A ...Col D
Name End
Andy 03 February 2005
Belinda 08 January 2000
Charlie 10 October 2002
Andy 03 February 2005

I'm sure the answer lies in VLOOKUP, or maybe it's INDEX/MATCHES?

Thanks in advance

Looking up a maximum value that matches criteria. VLOOKUP?
 

one way to do this is

sort the entire list on fist name asced and then data descnd
then in a different range enter the names
and now use match, index and offset to get the maximum date

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"AndyCotgreave" wrote:

Hi,
I'm trying to do a VLOOKUP, but I need the maximum value of a
particular column. For example, in the data below:

Col A Col B Col C
Name Start End
Andy 01 January 1997 03 February 2005
Belinda 02 April 1996 08 January 2000
Charlie 03 February 2001 10 October 2002
Andy 01 January 1997 01 March 2004

In column D I want the maximum End date for each person. So, for the
above:
Col A ...Col D
Name End
Andy 03 February 2005
Belinda 08 January 2000
Charlie 10 October 2002
Andy 03 February 2005

I'm sure the answer lies in VLOOKUP, or maybe it's INDEX/MATCHES?

Thanks in advance

Looking up a maximum value that matches criteria. VLOOKUP?
 

One way ..

Put in D2 and array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(C$2:C$100,MATCH(MAX(IF(A$2:A$100=A2,C$2:C$1 00)),IF(A$2:A$100=A2,C$2:C$100),0))
Copy D2 down. Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AndyCotgreave" wrote in message
ups.com...
Hi,
I'm trying to do a VLOOKUP, but I need the maximum value of a
particular column. For example, in the data below:

Col A Col B Col C
Name Start End
Andy 01 January 1997 03 February 2005
Belinda 02 April 1996 08 January 2000
Charlie 03 February 2001 10 October 2002
Andy 01 January 1997 01 March 2004

In column D I want the maximum End date for each person. So, for the
above:
Col A ...Col D
Name End
Andy 03 February 2005
Belinda 08 January 2000
Charlie 10 October 2002
Andy 03 February 2005

I'm sure the answer lies in VLOOKUP, or maybe it's INDEX/MATCHES?

Thanks in advance

Looking up a maximum value that matches criteria. VLOOKUP?
 

Put in D2 and array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(C$2:C$100,MATCH(MAX(IF(A$2:A$100=A2,C$2:C$1 00)),IF(A$2:A$100=A2,C$2:*C$100),0))
Copy D2 down. Adapt the ranges to suit
--

Brilliant!
I've never managed to get my head round that level of complexity in a
formula. I can understand each individual bit, but struggle to write
that kind of thing myself.
Thanks
Andy

Looking up a maximum value that matches criteria. VLOOKUP?
 

welcome, Andy.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AndyCotgreave" wrote:

Brilliant!
I've never managed to get my head round that level of complexity in a
formula. I can understand each individual bit, but struggle to write
that kind of thing myself.
Thanks
Andy