Index and Match: Fetch First and Last value

[Ashhu]

Hello
I am trying to fetch Entry and Exit time details of particular person. I am able to fetch the Entry details without any issue using Index and Match by considering the multiple conditions namely, Name and Date. But the problem I am facing is to fetch Exit or last entry made by person. I am unable to face do match of last event logged.
Please see attached sheet. I have log sheet from B2:D20, I will manually pick name in cell C24 and Date in C25, I am am able to fetch the IN details in cell C27 but last entered details in cell C28 is not able to fetch. Please guide and help.

 

[Khalid NGO]

Hi Ashhu,
Your time stamps are not real. First you need to convert them into real numbers so it can be calculated.
use a helper column, in E3:
=--SUBSTITUTE(D3,".","")
copy down till E20

Now use this array formula for Exit:
=INDEX(E3:E20,MATCH(MAX(IF(B3:B20=C24,IF(C3:C20=C25,E3:E20))),E3:E20,0),)
with Ctrl+Shift+Enter

Regards,

 

[Ashhu]

It working great ,
Thanks Bro Khalid, I appreciate.

 

[Khalid NGO]

It working great ,
Thanks Bro Khalid, I appreciate.

Welcome dear.
Thanks for feedback.
Keep visiting.

 

[Ashhu]

Welcome dear.
Thanks for feedback.
Keep visiting.

Khalid
i have one more request, I want to display all the entries made by person on particular selected date one below one. How can we do that?

 

[Khalid NGO]

Khalid
i have one more request, I want to display all the entries made by person on particular selected date one below one. How can we do that?

Might be this:
=INDEX($E$3:$E$20,MATCH(SMALL(IF($B$3:$B$20=$C$24,IF($C$3:$C$20=$C$25,$E$3:$E$20)),ROW(A1)),$E$3:$E$20,0),)

or with IFERROR:
=IFERROR(INDEX($E$3:$E$20,MATCH(SMALL(IF($B$3:$B$20=$C$24,IF($C$3:$C$20=$C$25,$E$3:$E$20)),ROW(A1)),$E$3:$E$20,0),),"")

with CSE.

Regards,

 

[Ashhu]

Might be this:
=INDEX($E$3:$E$20,MATCH(SMALL(IF($B$3:$B$20=$C$24,IF($C$3:$C$20=$C$25,$E$3:$E$20)),ROW(A1)),$E$3:$E$20,0),)

or with IFERROR:
=IFERROR(INDEX($E$3:$E$20,MATCH(SMALL(IF($B$3:$B$20=$C$24,IF($C$3:$C$20=$C$25,$E$3:$E$20)),ROW(A1)),$E$3:$E$20,0),),"")

with CSE.

Regards,

Many Thanks again, you are saver.