lookup max date on list

[koi]

hi all,

need help to lookup max date on list

A1:A5 = A
A6:A10 = B

B1:B10 = date

i need some formula in C to show which A and Which B got max date.

yesterday i make it using pivot and just show as max..but i wanted to know how to do it using formula as well, thanks

[zbor]

try =MAX(B1:B10) and format as date

[koi]

hi,

actually you cannot do that since it will return to the max date, but i wanted to show max date for each A and each B.

kinda need something in column C like count a duplicate so it will show example 1 in highest date for A and B so i can just sort it later

[zbor]

use =MAX(A1:A5) and same for other.

or upload example workbook so we can see what you trying to do.

[koi]

hi, this is the workbookBook1.xls

[zbor]

Try this:

=MAX(IF($A$2:$A$100=A2,$B$2:$B$100))

comfirmed with ctrl+shift+enter (and not just enter)

[Ace_XL]

Use..

=MAX(IF($A$2:$A$22="A",$B$2:$B$22))

Confirm with Ctrl+Shift+Enter and not just Enter

Repalce "A" with "B" or "C" to get desired results

[koi]

hi,

still not working, if the A,B,C is too many..then i need to change that variable all the time also in C all A now showing the max date..how to sort it out then?

[zbor]

Try this:

=IF(MAX(IF($A$2:$A$100=A2,$B$2:$B$100))=B2,B2,"")

[Ace_XL]

Try this..

=IF(B2=MAX(IF($A$2:$A$22=A2,$B$2:$B$22)),B2,"")

Confirm with Ctrl+Shift +Enter

[koi]

thats perfect.. thanks so much, one question.. can we make it without array bracket? and why do you know it has to be with array?

dont really like using array since sometimes when i send to people they tend to screw it