| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting data that matches certain criteria in one column | Excel Discussion (Misc queries) | |||
| How do I count cells that matches multiple criteria in Excel? | Excel Worksheet Functions | |||
| V-lookup and summing values if more than 1 matches criteria | Excel Worksheet Functions | |||
| SumIf Criteria Matches | Excel Worksheet Functions | |||
| Is it possible to do a vertical lookup that matches on 2 criteria | Excel Worksheet Functions | |||
Linear Mode
