Want MAX Date

[JAMIR]

Hello Everyone,

I have uploaded sample file. I wan MAX date from column A of Enter Vehicle No. i.e. MH14BT0368 is having two date 01-04-17 & 01-06-17, So i want 01-06-17.

Thanks & Regards,


Zameer

 

[Faseeh]

Please use this:

=MAX(IF($B$2:$B$10=D3,$A$2:$A$10))

with CSE.

 

[JAMIR]

Please use this:

=MAX(IF($B$2:$B$10=D3,$A$2:$A$10))

with CSE.

Thanks a lot... Its working....

Regards,


Zameer

 

[bosco_yip]

Try this non CSE formula :

=LOOKUP(2,1/(B1:B100=D3),A1:A100)

or,

=AGGREGATE(14,6,A1:A100/(B1:B100=D3),1)

Regards
Bosco

 

[Suman Banerjee]

Hi Bosco
Can you please explain the formula for my sake. I couldn't understand why 2 is in place of "Lookup value" and why (B1:B100=D3) is being divided by 1.

Regards
SB

 

[bosco_yip]

Hi Bosco
Can you please explain the formula for my sake. I couldn't understand why 2 is in place of "Lookup value" and why (B1:B100=D3) is being divided by 1.

Regards
SB

LOOKUP(2,1/(B1:B100=D3),A1:A100)

The condition (B1:B100=D3) after coercing returns 1 for match and 0 for no match

and become :

{0;0;0;1;0;0;1;0;0;0;0…….}

Divided by 1 >> 1/(B1:B100=D3) it become :

{#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!......}

2 is the Bignum always greater than 1, LOOKUP will ignore error and return the last numerical value i.e. the last 1, representing the last match.

Regards
Bosco

 

[Suman Banerjee]

THANKS A LOT BOSCO