VLOOKUP with time range conditions?

[RyanHillAZ]

Hello Everyone,

I am trying to figure out how to write a formula to verify if a bus made it to the scheduled stop within a given window of time.

My data shows the bus schedule and it also shows any bus that arrived within the given landmark (geo-fence) within a period of time.

I would like to be able to say "If any vehicle arrived within the landmark stop (ex. 411 Stop C @ 9:15 AM) at least 30 minutes prior to 9:15 AM and within 5 minutes after 9:15 AM."

I am not even close to figuring this one out. The nearest reference that I could find on the message board was the post of "VLOOKUP with Time Range" - http://www.mrexcel.com/forum/excel-questions/384955-vlookup-time-range.html

But I am not proficient with INDEX or MATCH functions.

Here is some of my sample data: This comes from a report showing when the bus actually arrived within the given landmark location.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VEHICLE[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]Landmark[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]First In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]Last In[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701
[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]5:42 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]5:47 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]6:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]6:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]7:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]8:42 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]8:46 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]9:20 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]9:21 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:17 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]10:50 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]411 stop C[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]11:45 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104, align: right"]11:47 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Here is the route information as to when the bus is supposed to be at the stop location.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Direction[/TD]
[TD]Landmark[/TD]
[TD]Scheduled Time[/TD]
[TD]Actual Time[/TD]
[TD](+,-)[/TD]
[TD]Decimal[/TD]
[TD](Y,N)[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]9:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD]=D2-C2[/TD]
[TD]=E2[/TD]
[TD]On Time?[/TD]
[TD]=IF((F2="Y"),1,IF((F2="N"),0))[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]10:16 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD]=D3-C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]1:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]2:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Northbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]5:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]9:15 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]10:15 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]1:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]2:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]Southbound[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="width: 110"]411 stop C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl66, width: 116, align: right"]5:15 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] ? formula ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Columns E, F, G, & H can be ignored in the above example. They are basically helper columns to convert the time into a decimal and identify if the bus made the stop within a given time.

The full data excel can be found on my google drive link if you would like to see the full landmark history sheet and the validation worksheet that I am working to create.

Excel Link: https://drive.google.com/file/d/0B-z07hmwXXmIeFJCaHp0V0tDVms/view?usp=sharing

Thanks so much!

Ryan Hill

 

[Cosmic Wizard]

Hi Ryan, sorry about the wait but just couldn't get everything to work just right.
Below is the link

https://drive.google.com/folderview...BDXzFUeTBnQTQyQ3VBb3VsQ2YxZTRJMlU&usp=sharing

Hope that work, it actually the first time I have used Google Drive.
I hope you understand it.

Actually just a quick note, these formulas only works only if there is at least one bus time against each scheduled time.
If you look in Validation sheet, top right hand corner, you will see that there is quite a variety of the number of times per stop, which really should not happen if the bus was travelling along the same route.
You can have more Landmark times, but never more Validation entries than Landmark. I presume this was just an example in the spreadsheet.
i.e. if Stop 1 has 12 scheduled times, but only 8 buses went through , Stop 6 has 26 schedules time but only 21 buses went through.
The data really should be if a bus went through one stop it will continue to the end through all stops.

 

[Cosmic Wizard]

Actually just a quick note, these formulas only works only if there is at least one bus time against each scheduled time.
If you look in Validation sheet, top right hand corner, you will see that there is quite a variety of the number of times per stop, which really should not happen if the bus was travelling along the same route.
You can have more Landmark times, but never more Validation entries than Landmark. I presume this was just an example in the spreadsheet.
i.e. if Stop 1 has 12 scheduled times, but only 8 buses went through , Stop 6 has 26 schedules time but only 21 buses went through.
The data really should be if a bus went through one stop it will continue to the end through all stops.

Sorry it actually doesn't work!

=IF(ISNA(VLOOKUP(D249,Landmark!$H$2:$I$212,2,FALSE)),INDEX(Landmark!$G$2:$G$212,MATCH(MIN(ABS(Landmark!$G$2:$G$212-Validation!C249)),ABS(Landmark!$G$2:$G$212-Validation!C249),0)),C249)

It picking up the nearest value to say 411 stop C 08:08 and not only stop C.

The problem is that we can find the nearest value in numeral terms, but the only way to separate the different bus stops in to put text in the cell.

And it doesn't like it.

Not sure how much time you have, but leave it with me.

We may have to split the bus stops up in data validation so the search parameters only use those stops.

 

[Cosmic Wizard]

Finally got it, can't belief it was this difficult, but the dates and formatting were totally hard.

Its at https://drive.google.com/file/d/0B11k9yh4DsmHX3QxbFRjY3NQcENnNFU5RU02MmVlTDBXX0pB/view?usp=sharing

Just one note, if you have more bus schedules times than arrivals, you will see the same bus arrival time crop up more than once.

You can highlight that with conditional formatting.

So sorry about the time it took, seriously never though it would be this hard! Enjoy!

(I had to combine the data one one sheet, you we will able to tell from which sheet the data came from by the heading colors)

 

[Cosmic Wizard]

The array formula is

{=INDEX($B$2:$B$212,MATCH(MIN(ABS(IF(C2=$A$2:$A$212,$B$2:$B$212-D2,9.99999999999999E+307))),ABS(IF(C2=$A$2:$A$212,$B$2:$B$212-D2,9.99999999999999E+307)),0))}

Just in case anyone wants it:

Can be found here we some other great array formulas Excel: Find closest value | Get Digital Help - Microsoft Excel resource

Have fun!