B1: =SUMPRODUCT(MAX((A1=Sheet1!D$1:D$12)*Sheet1!E$1:E$ 12))

If you will not have duplicates for the highest (if it is impossible that
Mike has a maximum of 7 and has it twice):

C1:
=SUMPRODUCT((Sheet1!F$1:F$12)*(Sheet1!$D$1:$D$12=$ A1)*(Sheet1!$E$1:$E$12=$B1))

If there might be two or more identical highest, this would choose the first
one to appear:

C1:
=INDEX(Sheet1!F$1:F$12,SUMPRODUCT(MIN(70000*((Shee t1!$D$1:$D$12<$A1)+(Sheet1!$E$1:$E$12<$B1))+ROW( Sheet1!F$1:F$12)*(Sheet1!$D$1:$D$12=$A1)*(Sheet1!$ E$1:$E$12=$B1))))

In both cases:

D1: copy from C1

These are not array formulae.

Glad if it helps,
Félix


"Heliocracy" wrote:

d'oh...so simple I should have thought of it. Thanks very much, Bob.

"Bob Phillips" wrote:

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?