B1: =MAX(IF(Sheet3!D1:D20=Sheet4!A1,Sheet3!E1:E20))
C1:
=INDEX(Sheet3!F$1:F$20,MATCH(1,(Sheet4!$A1=Sheet3! $D$1:$D$20)*(Sheet4!$B1=Sheet3!$E$1:$E$20),0))
D1:
=INDEX(Sheet3!G$1:G$20,MATCH(1,(Sheet4!$A1=Sheet3! $D$1:$D$20)*(Sheet4!$B1=Sheet3!$E$1:$E$20),0))

copy these down.

whichare all array formula, they should be committed with Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Heliocracy" wrote in message
...
Please help, I need to execute a complex MAX and LOOKUP.

I have a worksheet set up like this:

col D col E col F col G
Mike 1 10/1/2007 10/23/2007
Mike 3 10/24/2007 2/1/2008
Mike 7 2/2/2008 2/21/2008
Mike 2 2/22/2008 3/17/2008
Phil 11 10/1/2007 10/19/2007
Phil 44 10/20/2007 3/13/2008
George 56 2/1/2008 3/14/2008
George 13 3/15/2008 3/19/2008

On a second worksheet, I start by putting all the names (one time each) in
column A. For each of these names in column A, I need to return the
largest
number that appears in column E of the above table, where column D matches
the name in col A of the new worksheet. Then, in cols C and D of the new
worksheet, I need the dates from cols F and G above which appear on the
same
row as the maximum number which has been returned to the col B of the new
worksheet.

The new worksheet would look like this:

col A col B col C col D
Mike 7 2/2/2008 2/21/2008
Phil 44 10/20/2007 3/13/2008
George 56 2/1/2008 3/14/2008

I'm at a loss so far...How do I go about making this happen?