# VLOOKUP with time range conditions?

#### RyanHillAZ

##### New Member
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.
VEHICLE
 Landmark

<tbody>
</tbody>
 First In

<tbody>
</tbody>
 Last In

<tbody>
</tbody>
701
411 stop C
 5:42 AM

<tbody>
</tbody>
 5:47 AM

<tbody>
</tbody>
701411 stop C
 6:20 AM

<tbody>
</tbody>
 6:20 AM

<tbody>
</tbody>
701411 stop C
 7:13 AM

<tbody>
</tbody>
 7:16 AM

<tbody>
</tbody>
701411 stop C
 7:50 AM

<tbody>
</tbody>
 7:50 AM

<tbody>
</tbody>

<tbody>
</tbody>
701411 stop C
 8:42 AM

<tbody>
</tbody>
 8:46 AM

<tbody>
</tbody>
701411 stop C
 9:20 AM

<tbody>
</tbody>
 9:21 AM

<tbody>
</tbody>
701411 stop C
 10:13 AM

<tbody>
</tbody>
 10:17 AM

<tbody>
</tbody>
701411 stop C
 10:50 AM

<tbody>
</tbody>
 10:50 AM

<tbody>
</tbody>
701411 stop C
 11:45 AM

<tbody>
</tbody>
 11:47 AM

<tbody>
</tbody>

<tbody>
</tbody>

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

DirectionLandmarkScheduled TimeActual Time(+,-)Decimal(Y,N)%
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 9:16 AM

<tbody>
</tbody>
? formula ?=D2-C2=E2On Time?=IF((F2="Y"),1,IF((F2="N"),0))
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 10:16 AM

<tbody>
</tbody>
? formula ?=D3-C3
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 1:16 PM

<tbody>
</tbody>
? formula ?
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 2:16 PM

<tbody>
</tbody>
? formula ?
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 5:16 PM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 9:15 AM

<tbody>
</tbody>

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 10:15 AM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 1:15 PM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 2:15 PM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 5:15 PM

<tbody>
</tbody>
? formula ?

<tbody>
</tbody>

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.

Thanks so much!

Ryan Hill

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
VEHICLE
 Landmark

<tbody>
</tbody>
 First In

<tbody>
</tbody>
 Last In

<tbody>
</tbody>
701411 stop C
 5:42 AM

<tbody>
</tbody>
 5:47 AM

<tbody>
</tbody>
701411 stop C
 6:20 AM

<tbody>
</tbody>
 6:20 AM

<tbody>
</tbody>
701411 stop C
 7:13 AM

<tbody>
</tbody>
 7:16 AM

<tbody>
</tbody>
701411 stop C
 7:50 AM

<tbody>
</tbody>
 7:50 AM

<tbody>
</tbody>

<tbody>
</tbody>
701411 stop C
 8:42 AM

<tbody>
</tbody>
 8:46 AM

<tbody>
</tbody>
701411 stop C
 9:20 AM

<tbody>
</tbody>
 9:21 AM

<tbody>
</tbody>
701411 stop C
 10:13 AM

<tbody>
</tbody>
 10:17 AM

<tbody>
</tbody>
701411 stop C
 10:50 AM

<tbody>
</tbody>
 10:50 AM

<tbody>
</tbody>
701411 stop C
 11:45 AM

<tbody>
</tbody>
 11:47 AM

<tbody>
</tbody>

<tbody>
</tbody>

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

DirectionLandmarkScheduled TimeActual Time(+,-)Decimal(Y,N)%
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 9:16 AM

<tbody>
</tbody>
? formula ?=D2-C2=E2On Time?=IF((F2="Y"),1,IF((F2="N"),0))
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 10:16 AM

<tbody>
</tbody>
? formula ?=D3-C3
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 1:16 PM

<tbody>
</tbody>
? formula ?
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 2:16 PM

<tbody>
</tbody>
? formula ?
 Northbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 5:16 PM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 9:15 AM

<tbody>
</tbody>

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 10:15 AM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 1:15 PM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 2:15 PM

<tbody>
</tbody>
? formula ?
 Southbound

<tbody>
</tbody>
 411 stop C

<tbody>
</tbody>
 5:15 PM

<tbody>
</tbody>
? formula ?

<tbody>
</tbody>

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.

Thanks so much!

Ryan Hill

Hi Ryan,

Haven't been able to finish this, so i would though i would show what I had done and see it someone can come up with a formal which works.

First you need to custom format the cell with this: (got this at https://support.office.com/en-ca/ar...-of-time-9a6558a1-a86b-41d4-b244-1000fc7d31e9)

On the Home tab, in the Cells group, click Format, and then click Format Cells.

In the Format Cells dialog box, click the Number tab.

Under Category, click Custom.

In the Type box, type [h]:mm.

TIP You can also show the results in minutes and seconds by setting the format to [m]:ss, or minutes only by typing [m].

Click OK.

Excel displays 28:15 if you used the format [h]:mm).

Then a simple =D2-C2 to get the time from the actual arrival to Scheduled Time (Format this column with the custom format as well)

Now have tried all sorts of AND and IF functions but can't get it to work.

It doesn't seem to like the 0:30 in anything I know. Let see it someone can figure it out!

Actually you need to use an ABS function in case the bus comes really early.

=ABS(C14-D14) will do it.

Next try this formula =IF(COUNT(E14),IF(E14>TIME(0,30,0),"Not On Time","On Time"),"")

Its working for me, but I have tried so many equations I have gone cabin crazy!

If it doesn't will go back to the drawing board!

Hi Cosmic Wizard,

Thank you so much for all of your work on this! I am not sure where to place the formulas you mentioned.

1. =ABS(C14-D14) where does this one go? What does it accomplish?

2.
=IF(COUNT(E14),IF(E14>TIME(0,30,0),"Not On Time","On Time"),"") where does this one go?

Thanks,

Ryan

Hi Cosmic Wizard,

Thank you so much for all of your work on this! I am not sure where to place the formulas you mentioned.

1. =ABS(C14-D14) where does this one go? What does it accomplish?

2.
=IF(COUNT(E14),IF(E14>TIME(0,30,0),"Not On Time","On Time"),"") where does this one go?

Thanks,

Ryan

Here we go:

Scheduled Time Actual Time Difference Result

9:16 AM 9:26 AM 0:10 On Time

Here we go:

Scheduled Time Actual Time Difference Result

9:16 AM 9:26 AM 0:10 On Time

Gotcha, that makes sense.

The part that I am still having trouble with is finding a formula which can review the data in the other table to determine time at which the bus entered the landmark zone from the other spreadsheet.

I am hoping there is a formula which can review the name of the landmark. [411 Stop C] then check the scheduled arrival time [9:19am] and review the data in the other spreadsheet to return a value of the nearest time to the scheduled time.

Gotcha, that makes sense.

The part that I am still having trouble with is finding a formula which can review the data in the other table to determine time at which the bus entered the landmark zone from the other spreadsheet.

I am hoping there is a formula which can review the name of the landmark. [411 Stop C] then check the scheduled arrival time [9:19am] and review the data in the other spreadsheet to return a value of the nearest time to the scheduled time.

Just a couple of quick questions;

Do you mean to say you just want a count of the number of times a bus was on time at Stop C??

If so a simple COUNTIF function will do:

On Time =COUNTIF(F2:F20,D24) where cell D24 has the text On Time in it; and F2:F20 is the column where the result are in

Not On Time =COUNTIF(F4:F22,D26) same as above.

Or you could use Data Validation so you can swap between Stops to find the data.

=COUNTIFS(F2:F20,D24,B2:B20,D28) is the formula to use, where D28 is the cell where your validation list is, and B2:B20 is the range of bus stops for your validation list.

Your could also your data validation for your "On Time" and "Not On Time" choices, to simplified the process.

Just a couple of quick questions;

Do you mean to say you just want a count of the number of times a bus was on time at Stop C??

If so a simple COUNTIF function will do:

On Time =COUNTIF(F2:F20,D24) where cell D24 has the text On Time in it; and F2:F20 is the column where the result are in

Not On Time =COUNTIF(F4:F22,D26) same as above.

Or you could use Data Validation so you can swap between Stops to find the data.

=COUNTIFS(F2:F20,D24,B2:B20,D28) is the formula to use, where D28 is the cell where your validation list is, and B2:B20 is the range of bus stops for your validation list.

Your could also your data validation for your "On Time" and "Not On Time" choices, to simplified the process.

No, I am not looking for the count of On Time arrivals.

I will try to better explain the first issue.

I have the bus schedule in one spreadsheet. I have the number of times any number of different buses entered the landmark location in which the bus stop is located in the next spreadsheet.

I am trying to write a formula which will look at the bus schedule spreadsheet and review 2 variables. 1) The name of the bus stop 2) The scheduled time of arrival. I then want to take that information (Stop = 411 Stop 1, Scheduled Time = 9:19 AM) and review the landmark history information to find the following.

Return a time in which any bus entered the 411 Stop 1 within a time frame of 30 minutes before 9:19 AM and within 5 minutes after 9:19 AM. So the formula would be checking the range of cells within the landmark history spreadsheet to try and find the nearest time to that (if there is one) and return the time to my bus stop spreadsheet.

 Direction Landmark Scheduled Time Actual Time (+,-) Decimal (Y,N) % Northbound 411 Stop 1 9:19 0:01:00 0.0007 Y 1 Northbound 411 Stop 1 10:19 10:19:00 0.4299 N 0 Northbound 411 Stop 1 13:19 13:19:00 0.5549 N 0 Northbound 411 Stop 1 14:19 14:19:00 0.5965 N 0 Northbound 411 Stop 1 17:19 17:19:00 0.7215 N 0 Northbound 411 Stop 1 18:19 18:19:00 0.7632 N 0 Southbound 411 Stop 1 9:13 9:13:00 0.3840 N 0 Southbound 411 Stop 1 10:13 10:13:00 0.4257 N 0 Southbound 411 Stop 1 13:13 13:13:00 0.5507 N 0 Southbound 411 Stop 1 14:13 14:13:00 0.5924 N 0 Southbound 411 Stop 1 17:13 17:13:00 0.7174 N 0 Southbound 411 Stop 1 18:13 18:13:00 0.7590 N 0 Southbound 411 Stop 10 6:21 6:21:00 0.2646 N 0 Southbound 411 Stop 10 7:06 7:06:00 0.2958 N 0

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 Vehicle VIN Serial Number Driver Landmark First In First In Last In Still In Distance (miles) Time Inside (hours) Idling Inside (hours) Stopped Inside (hours) First Odometer Last Odometer Heading 765 1FDFE4FS9DDB19476 4641293469 not set 411 stop 1 5/26/15 12:00 AM 10:12 10:12 AM 0 0 0 0 0 75,512.50 75,512.50 N 765 1FDFE4FS9DDB19476 4641293469 not set 411 stop 1 5/26/15 12:00 AM 13:15 1:15 PM 0 0 0 0 0 75,555.60 75,555.60 N 765 1FDFE4FS9DDB19476 4641293469 not set 411 stop 1 5/26/15 12:00 AM 14:18 2:18 PM 0 0 0 0 0 75,575.90 75,575.90 N 765 1FDFE4FS9DDB19476 4641293469 not set 411 stop 1 5/26/15 12:00 AM 17:10 5:10 PM 0 0 0 0 0 75,618.60 75,618.60 N 767 1FDFE4FS6DDB27454 4641294457 not set 411 stop 1 5/26/15 12:00 AM 7:06 7:06 AM 0 0 0 0 0 97,109.60 97,109.60 N 767 1FDFE4FS6DDB27454 4641294457 not set 411 stop 1 5/26/15 12:00 AM 11:12 11:12 AM 0 0 0 0 0 97,222.20 97,222.20 N 767 1FDFE4FS6DDB27454 4641294457 not set 411 stop 1 5/26/15 12:00 AM 12:16 12:16 PM 0 0 0 0 0 97,257.00 97,257.00 N 767 1FDFE4FS6DDB27454 4641294457 not set 411 stop 1 5/26/15 12:00 AM 18:33 6:33 PM 0 0 0 0 0 97,428.00 97,428.00 N

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Return a time in which any bus entered the 411 Stop 1 within a time frame of 30 minutes before 9:19 AM and within 5 minutes after 9:19 AM. So the formula would be checking the range of cells within the landmark history spreadsheet to try and find the nearest time to that (if there is one) and return the time to my bus stop spreadsheet.

Stop if I am on the wrong tract, but you want the nearest time to 9:19 over say the course of a day, 30 mins before and 5 mins after.

So basically one time which is the closest to 9:19, like 9:21???

Stop if I am on the wrong tract, but you want the nearest time to 9:19 over say the course of a day, 30 mins before and 5 mins after.

So basically one time which is the closest to 9:19, like 9:21???

Yes, that is exactly what I am looking for. Thanks so very much!

Replies
13
Views
255
Replies
5
Views
66
Replies
0
Views
232
Replies
9
Views
106
Replies
1
Views
78

1,196,487
Messages
6,015,506
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back