look up first and last instance

[skate1991]

HI,

Please see the attached example.

On tab 1 we have data validation in cell A1 which is a date.

On tab 2 we have a record of when someone logs on.

What I'm looking for is a lookup which looks at the date and the user and gives the date and time of when they first and last logged in.

So for example on the 3/9/2013, First would be 03/09/2013 03:17 and last would be 03/09/2013 23:45

Thanks in advance

[samba_ravi]

see the attachment

[ramananhrm]

For Login - in cell C4
=SMALL(IF(DATE(YEAR(Sheet2!$C$2:$C$11),MONTH(Sheet2!$C$2:$C$11),DAY(Sheet2!$C$2:$C$11))=$A$1,Sheet2!$C$2:$C$11),1)

&

For Logout - In cell D4
=LARGE(IF(DATE(YEAR(Sheet2!$D2:$CD$11),MONTH(Sheet2!$D$2:$D$11),DAY(Sheet2!$D$2:$D$11))=$A$1,Sheet2!$D$2:$D$11),1)

Please paste both with Ctrl+Shift+Enter

[ragulduy]

Another:

C4
=MIN(IF(Sheet2!A2:A11=Sheet1!B4,Sheet2!C2:C11,100000)*IF(ROUNDDOWN(Sheet2!C2:C11,0)=Sheet1!A1,1,100000))

D4:
=MAX(IF(Sheet2!A2:A11=Sheet1!B4,Sheet2!C2:C11,0)*IF(ROUNDDOWN(Sheet2!C2:C11,0)=Sheet1!A1,1,0))

confirm with ctrl+shift+enter

[skate1991]

Thanks everyone for your help

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.