Finding Max or Min Value

[Guest]

I just cant find a way to bring bring the max value over from sheet 1 to
sheet 2.

Sheet 1
A-----B------------C (header)
1-----W65--------00-Jan-00
2-----W65--------00-Jan-00
3-----W65--------21-Nov-06
4-----W65--------22-Nov-06
5-----W65--------24-Jul-06
6-----W65--------24-Jul-06
7-----U54--------20-Sep-06
8-----U56--------00-Jan-00
9-----U56--------00-Jan-00
10----U56--------00-Jan-00
11----U56--------00-Jan-00
12----U56--------22-Nov-06

Sheet 2
W65-------->?? (index match would result in 00-Jan-00 & vlookup-true, a
weird number) & I am unable to operate the Dmax formula.

The answer is: 22-Nov-06 (manually)

U56--------->??

The answer is: 22-Nov-06 (manually)


Help is needed!!!!!


Thanks.

 

[Bob Phillips]

=MAX(INDEX((Sheet1!B1:B12=A1)*Sheet1!C1:C12,0))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I've typed the formula as follow:
=max(index(sheet1A:A=Sheet2A1)*(sheet1!B:B,0))

What went wrong?


Sheet 1
+------A-------------B-----(header)
1-----W65--------00-Jan-00
2-----W65--------00-Jan-00
3-----W65--------21-Nov-06
4-----W65--------22-Nov-06
5-----W65--------24-Jul-06
6-----W65--------24-Jul-06
7-----U54--------20-Sep-06
8-----U56--------00-Jan-00
9-----U56--------00-Jan-00
10----U56--------00-Jan-00
11----U56--------00-Jan-00
12----U56--------22-Nov-06

Sheet 2 (additional Information)
Cell A1 & A2 are index(match) formula.

W65--(A1)---->?? (index match would result in 00-Jan-00 & vlookup-true, a
The answer is: 22-Nov-06 (manually)

U56--(A2)---->??
The answer is: 22-Nov-06 (manually)

 

[Biff]

=max(index(sheet1A:A=Sheet2A1)*(sheet1!B:B,0))

Try it like this: (you can't use entire columns - A:A, B:B)

=MAX(INDEX((Sheet1!A1:A100=Sheet2!A1)*Sheet1!B1:B100,0))

Biff

 

[Guest]

Thank you ALL!!!!, I will check it out on Monday. Have a superb weekend.