• 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.

Time Range Lookup

P

Pavan Kumar Sistla

Member
Dear All,

Based on the attached excel, there are two sheets. The Master sheet has Vehicle ID and driver names whose login and logout times are given. In the Report sheet, I need the driver name to be populated based on: a) Date, b) Vehicle No. c) Time which are in A, B & C columns respectively.

Could you please help with formula.

Thank You and Regards,
Pavan.
 

Attachments

  • Time Range Lookup.xlsx
    10 KB · Views: 15
In cell E2 of your Report sheet, array-enter the following (array-entering means committing the formula to the sheet usinmg Ctrl+Shift+Enter, not just Enter):
=IFERROR(INDEX(Master!$C$1:$C$25,SMALL(IF((Master!$A$1:$A$25=$A2)*($C2>Master!$D$1:$D$25)*($C2<=Master!$E$1:$E$25)>0,ROW($A$1:$A$25)),COLUMN()-4)),"")

Then you can copy down and across (across too, because, as is the case with your data, there can be several records fitting the criteria).

If you're sure there can be no more than one record matching at a time then you can shorten it to an array-entered:
=IFERROR(INDEX(Master!$C$1:$C$25,MATCH(1,(Master!$A$1:$A$25=$A2)*($C2>=Master!$D$1:$D$25)*($C2<=Master!$E$1:$E$25),0)),"")

[I was called away from my machine and didn't notice Alan had responded in the meantime]
 
Who are you addressing this to? Give us an example of what changes in your worksheet? Be specific to your request. Generic requests are to vague to offer specific responses and solutions.

If you are looking for a specific time of an incident, why would the time change. Give us a realistic business action that would cause this.
 
Dear Alan,

Thank you for the kind response. Attached is the excel with the input of formulas given by you and p45cal. Could you please look into these and if the time is changed in the 1st sheet, the correct result is not being picked up.

Thank You again and Regards,
Pavan.
 

Attachments

  • Time Range Lookup.xlsx
    12.5 KB · Views: 8
Dear Alan,

The Time Out and Time In are the shift timings of drivers who drive the same vehicle in different shifts. Based on the timing of the fine, the driver name needs to be identified. For example: If a driver starts shift at 8:00 PM, and the fine time is @ 8:20 PM, the portion of the formula : ($C4>=Master!$D$2:$D$25)*($C4<=Master!$E$2:$E$25) is giving #N/A based on formula evaluation and so is the reason for the empty result in the formula. Could you please check and kindly help.

Thank You and Regards,
Pavan.
 
Did you test with my code or just P45cal? Your issue is not with my code and I don't know if you have issue with that formula. If so, please explain in detail the issue.
 
Thank You Alan. However, please note that the Index and Match combination of the formula you have suggested is working only in parts and it is retrieving #N/A error whenever the time slot is present in column E instead of column D which is specified in the formula.

Regards,
Pavan.
 
You have Time Out after midnight occasionally.
Test this in E2, array-entered and copied down:
=IFERROR(INDEX(Master!$C$1:$C$25,MATCH(1,(Master!$A$1:$A$25=$A2)*($C2>=Master!$D$1:$D$25)*($C2<=((Master!$E$1:$E$25)+(Master!$E$1:$E$25<Master!$D$1:$D$25))),0)),"")
 
You must log in or register to reply here.
Back
Top