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

Vlookup Next time event

W

webtronix

New Member
Hi, This is my very first post, so apologies if I miss something, but will try to cover and clarify as much as I can.

I have 2 excel workbooks. The first one has a list of names,dates and times and the other workbook has a list of names, dates and times also.

The times in WB 1 is when a message was received. the times in WB 2 is a range by names and what they did on a particular day ( Timesheet).


What I want to do is look up the name on the respective date and find out what that person did after the message was received.

i.e. John - 22/12/12 - 15:35


next workbook

John - 22/12/12 - 08:00 - travel 1

john - 22/12/12 - 09:00 - park 1

john - 22/12/12 - 10:10 - break 1

john - 22/12/12 - 11:30 - travel 2

john - 22/12 12 - 12:50 - park 2

john - 22/12/12 - 14:30 - travel 2

john - 22/12/12 - 15:50 - park 3

john - 22/12/12 - 16:00 - travel 3

john - 22/12/12 - 16:30 - home

peter- 22/12/12 - 08:00 = travel


and so on......

the lookup needs to return the event after 15:35, which would be 'park 3' at 15:50


is this do-able?, and if so, in excel formula, or would it need to be VBA?


i have created a unique key in column A in both worksheets such as john22/12/12, which i have tried to to a vlookup, but it only returns the first row, i need another parameter that would be next event time 'greater than'


Appreciate you guys looking at this :):)


regards- Ash
 
Thanks for replying Jason. I've thought of index & match too, but its would only return what it matches directly. i need it to look for the next time above, and as 15:50 would not directly exist in the time sheet WB then it would not be able to match .... correct me if I'm wrong..
 
Hi Ash ,


Try this :


=IFERROR(INDEX(Events,SMALL(IF(Names=Selected_Name,IF(Dates=Selected_Date,IF(Dates+Times>=Selected_Date+Selected_Time,ROW(Events)-MIN(ROW(Events))+1))),ROW(A1))),"")


entered as an array formula , using CTRL SHIFT ENTER.


Copy it down , as far as you want.


Narayan
 
Thanks Narayan


this is one heck of a long formula. i presume you have referred to 'events' as a named range
 
Hi Ash ,


Yes ; each of the following : Events , names , Dates and Times is a named range , while Selected_Name , Selected_Date and Selected_Time are all named cells.


Narayan
 
Hi Narayan,


I've tried your formula but am getting #ref!. what would cell A1 refer to?


am i able to attached the sample workbook?
 
A1 does not need to change to anything. It does need to be A1 in the first cell. It's being used as a counter. The ROW(A1) will evaluate to 1. In the next cell down, it evaluates to 2, then to 3, etc.
 
hmmmn. still getting #ref!.... i have a feeling that the named ranges might not be looking at the right sections, but have tried all possible combinations.( named cells are correct as have checked this using other simple formulas)....


to check i did remove 'iferror'...
 
Phew, glad you were able to get it working. Let us know if you run into any other questions! =)
 
What i did notice that the timesheet data needs to be in date / time order, otherwise it tends to fall over...and the named ranges also needs to be aligned correctly otherwise you end up getting the wrong row detail. if anyone is going to replicate this, please check and validate random rows / cells. A big thanks to all above for your support :)
 
You must log in or register to reply here.
Back
Top