Max function with lookup

[amna77]

Hi, I am using the max formula like max(B1:B10) to get the highest value.
but I want to look into column C as well. In Column C I have numbers, like
Column B Column C
12/05 1
12/05 3
12/9 3
12/6 2

now I want to get the max value from column B, but my look up number is 3. so basically chek out the value in column C, like how many number 3 I have and then give me max value for number 3 from column B.
Like my answer will be 12/9
Any help?
thanks in advance

 

[amna77]

Columns data was kind of messed up, so here it is again.

ColumnB------ Column C
12/05----------- 1
12/05 -----------3
12/9 ------------3
12/6 ------------2

 

[Juan Pablo González]

There are two 3's in there, so, why do you choose for the second ? Or do you choose the maximum date always ?

 

[dk]

Hi,

One possibility is with an array formula. The difference with one of these is that you enter it with Ctrl - Shift - Enter, not just enter. For example:-

Book1
	B	C	D	E	F
1	ColumnB	ColumnC
2	12/05/2002	1		Formulahere>>>	12/09/2002
3	12/05/2002	3
4	12/09/2002	3
5	12/09/2002	2
Sheet1

 

[amna77]

That example I gave you its dummy, in the real data my sheet is so huge. so I always have to go my max date.

Thanks for array function, but that Each time I have press Ctrl, Shift and enter keys to update.
Is there any different way to do it? I mean after pluging the formula,then I don't have to press any keys for update.

Thanks for all help
Thanks

 

[dk]

You don't have to press Ctrl shift and enter each time. It will update in the same way as any other normal formula. Try it...