Extract Data with one Criteria

[JAMIR]

Hello Respected Ninjas

I have attached Sample File. There is Vehicle Register & RC Register. In RC Register i want to get Vehicle Out Date from Vehicle Register which is latest.

i.e. MH15AK8012 is out on 11-04-16 & 25-04-16. And i want 25-04-16 not 11-04-16.


Warm Regards


Zameer Shaikh

 

[NARAYANK991]

Hi Zameer ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=MAX(IF(Table1[Vehicle No.]=B2,Table1[Vehicle Out Date]))

Narayan

 

[JAMIR]

Hello Narayan Sir,

After such long time. How are you? Hope you'll fine. Once again big thanks for such great help.

Take Care..


Warm Regards,


Zameer

 

[Hui]

rc Register E2: =MAX(IF(Table1[Vehicle No.]=B2,ROW(Table1[Sr. No.]),)) Ctrl+Shift+Enter

 

[Naresh Yadav]

Dear Jamir,

you can also try below formula if you are using excel-2010

=AGGREGATE(14,6,Table1[Vehicle Out Date]/(--(Table1[Vehicle No.]=[@[Vehicle No.]])),1)

 

[JAMIR]

rc Register E2: =MAX(IF(Table1[Vehicle No.]=B2,ROW(Table1[Sr. No.]),)) Ctrl+Shift+Enter

Hello Hui Sir,

There is something going wrong. Narayan Sir has solve the issue.

But a very grateful thanks for help me.

Take Care.


Warm Regards,

Zameer

 

[JAMIR]

Dear Jamir,

you can also try below formula if you are using excel-2010

=AGGREGATE(14,6,Table1[Vehicle Out Date]/(--(Table1[Vehicle No.]=[@[Vehicle No.]])),1)

Hello Naresh

Thanks Brother for helping me. But i'm using office 2007.

Thanks once again.

Regards,


Zameer

 

[bosco_yip]

Or this non-array formula :

=1/LOOKUP(1,1/Table1[Vehicle Out Date]/(Table1[Vehicle No.]=B2))

Regards
Bosco

 

[Ashhu]

Hi Zameer,
You already got plenty of solution, one from my side
=SUMPRODUCT(MAX(--('vEHICLE rEGISTER'!B2:B100=B2)*'vEHICLE rEGISTER'!E2:E100))

-Have good day
Ashwin

 

[JAMIR]

Hello bosco_yip & Ashhu

I;m very glad. God bless all of you...

Have wonderful day...

Regards,


Zameer