Complex MAX and Lookup

[Heliocracy]

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?

 

[FiluDlidu]

Hi Heliocracy,

I would have some ideas about your problem, but your dates are not presented
under the same format as the one I use.

Why don't you resubmit your data using straight numbers instead?

(Okay! I understand that there are loads of people from the States here that
will have the same date format as you and many are also talented enough to
help you... You will probably not need to do it this time before you get an
answer... But I just wanted to let you know what I bumped into fiddling
around with your data: maybe next time you can change it right from the start
and POSSIBLY get an answer earlier...)

Cheers,
Félix

 

[Bob Phillips]

B1: =MAX(IF(Sheet3!D120=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]

The contents (and therefore the format) of columns F and G are irrelevant.
If it helps, pretend they are random numbers or words instead.

Thanks,
Heliocracy

 

[Heliocracy]

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

 

[FiluDlidu]

B1: =SUMPRODUCT(MAX((A1=Sheet1!D$1$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*((Sheet1!$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