Microsoft Excel - excel formula - Asked By usha anu on 21-May-14 02:51 AM

hi,
 i need one excel formula for below condition  in sheet one i have base data now i need in sheet 2 against SERIALNO last ticket no  and date .

Sheet 1
Serial No	Ticket No	Ticket Date
39173	135	10-Jan-13
39173	456	23-Jan-13
39173	856	30-Jan-13
Sheet-2
Serial No	Ticket No	Ticket Date
39173	856	30-Jan-13

Hello Anu
This will give it to you provided that ticket number and date are both in ascending order.  Enter in B2 and you can copy across.  Obviously the ranges will have to be set to suit your real data.
=MAX((Sheet2!$A2=Sheet1!$A$2:$A$4)*Sheet1!B$2:B$4)  Enter with Ctrl/Shift/Enter (Array formula)
Otherwise, you might have to use MATCH and OFFSET to get the appropriate date in C2.
=OFFSET(Sheet1!$C$1,MATCH(B2,Sheet1!B$2:B$4,0),0)
Hopefully this should get you on the right track.
Harry

Hello Anu,
This formula for the ticket number does not need to be entered as an array formula.
=SUMPRODUCT((A2=Sheet1!$A$2:$A$4)*1,(MAX(Sheet1!$B$2:$B$4)=Sheet1!$B$2:$B$4)*(Sheet1!$B$2:$B$4))
Regards
Harry