• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match: Fetch First and Last value

Ashhu

Active Member
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.
 

Attachments

  • Time Log.xlsx
    11.7 KB · Views: 9
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,
 
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,
 
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.
 
Back
Top