VLOOKUP with time range conditions?

R

RyanHillAZ

New Member
Joined
Oct 31, 2014
Messages
10
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.

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

Thanks so much!

Ryan Hill
 
Sort by date Sort by votes

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Cosmic Wizard said:
You can use this =MIN(Sheet2!H8:H97)

But this formula will not show if there are two of more values which are the same, just the lowest number.

Is that what you need??
Click to expand...


No, that won't work.


I need to figure out 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: for example [Stop = 411 Stop 1 & Scheduled Time = 9:19 AM] and review the landmark history information to find the following value.

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.

Does that make sense?
 
Upvote 0
RyanHillAZ said:
No, that won't work.


I need to figure out 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: for example [Stop = 411 Stop 1 & Scheduled Time = 9:19 AM] and review the landmark history information to find the following value.

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.

Does that make sense?
Click to expand...


Got it, or at least i hope so!!

Use data validation to make a list of all bus stops, list below.

411 Stop C 9:16:00 AM
411 Stop D 10:16:00 AM
411 Stop E 11:16:00 AM

Then in the cell next to it, put this array formula: {=MIN(IF(A2:A25=F12,D2:D25))}

Column A Column B Column C Column D
Bustop & Time Needed Scheduled Time Actual Time Difference
411 Stop C 9:16:00 AM 9:16 AM 9:06 AM 0:10

F12 is where your data validation list is.


It works for me, try it out and get back to me!
 
Last edited:
Upvote 0
I am not sure how to put your criteria "30 minutes before 9:19 AM and within 5 minutes after 9:19 AM" into Column D.
Date and time can not be negative, as they will not display, and that why we use the ABS function.
Having a look now.
 
Last edited:
Upvote 0
Cosmic Wizard said:
I am not sure how to put your criteria "30 minutes before 9:19 AM and within 5 minutes after 9:19 AM" into Column D.
Date and time can not be negative, as they will not display, and that why we use the ABS function.
Having a look now.
Click to expand...

Actually made a mistake in Column D: use this formula instead =IF(OR((B2-C2)*60*24>=30,(B2-C2)*60*24<5),"",(B2-C2)*60*24).

This was pointed out to me by konew1 in other thread on this issue:

Need a formula which can count minutes between two criteria of time


In a new column, put this formula, which will get rid of the negative values =IF(E2="","",ABS(E2)).

Then you can use data validation and the array formula {=MIN(IF(A2:A25=F12,D2:D25))} and that will give you the bus stop and nearest time.

Or I should say it working fine for me, but I think we actually have the answer in those three formulas!!!!
 
Upvote 0
Cosmic Wizard said:
Actually made a mistake in Column D: use this formula instead =IF(OR((B2-C2)*60*24>=30,(B2-C2)*60*24<5),"",(B2-C2)*60*24).

This was pointed out to me by konew1 in other thread on this issue:

Need a formula which can count minutes between two criteria of time


In a new column, put this formula, which will get rid of the negative values =IF(E2="","",ABS(E2)).

Then you can use data validation and the array formula {=MIN(IF(A2:A25=F12,D2:D25))} and that will give you the bus stop and nearest time.

Or I should say it working fine for me, but I think we actually have the answer in those three formulas!!!!
Click to expand...

Is there any chance you could upload your example in a spreadsheet? I cannot seem to get it to work.
 
Upvote 0
RyanHillAZ said:
Is there any chance you could upload your example in a spreadsheet? I cannot seem to get it to work.
Click to expand...

Which part is not working?
Can't seem to upload the spreadsheet so will do so below.
It is the array formula?
Will go through all the steps just in case.

1. In Column A paste all the bus stops individual times, your Landmark Column, see below
2. Format Columns B and C with a custom format of h:mm AM/PM, paste the Scheduled Time and Actual Time
3. In Column D, headed Difference, paste this formula =IF(OR((B2-C2)*60*24>=30,(B2-C2)*60*24<5),"",(B2-C2)*60*24), copy down.
4. In Column E, headed No Negative, paste this formula =IF(D2="","",ABS(D2)): this removes any negative number which would distort finding the closest time

This then provides us in Column E with how much time a bus was either 30 mins early or 5 mins past the designate stop time. It returns a blank cell if the time is outside those two parameters.

The next step in a group of cells is to paste the various Bus Stop times, remove the duplicates times, so you just have bus stops times like below; this will form your data validation list.

411 Stop C 9:16:00 AM
411 Stop D 10:16:00 AM
411 Stop E 11:16:00 AM


Form your data validation list, with in this case three examples.

Next use the array formula in the cell, {=MIN(IF(A2:A25=G12,E2:E25))}, remembering to press Control, Shift, Enter.

This formula looks up all the Bus Stop time in Column A A2:A25, the data validation list is in G12, so which ever time you choose from the drop down, say 411 Stop C 9:16:00 AM, that will be the time it looks up, and finds the minimum value available in Column E.

If the columns are in another sheet, you will need to include the sheet name like this {=MIN(IF(Sheet5!A2:A25=Sheet6!I3,Sheet5!E2:E25))}, again with Control, Shift, Enter.

Hope this make sense: if not post again: this so works!


Bus Stop Scheduled Time Actual Time Difference No Negative
411 Stop C 9:16:00 AM 9:16 AM 9:06 AM 10 10
411 Stop C 9:16:00 AM 9:16 AM 9:16 AM 0 0
411 Stop C 9:16:00 AM 9:16 AM 9:11 AM 5 5
411 Stop C 9:16:00 AM 9:16 AM 9:17 AM -1 1
411 Stop C 9:16:00 AM 9:16 AM 10:16 AM
411 Stop C 9:16:00 AM 9:16 AM 9:46 AM
411 Stop C 9:16:00 AM 9:16 AM 9:46 AM
411 Stop C 9:16:00 AM 9:16 AM 9:23 AM
411 Stop C 9:16:00 AM 9:16 AM 9:34 AM
411 Stop C 9:16:00 AM 9:16 AM 9:32 AM
411 Stop C 9:16:00 AM 9:16 AM 9:03 AM 13 13
411 Stop C 9:16:00 AM 9:16 AM 9:06 AM 10 10
411 Stop D 10:16:00 AM 10:16 AM 10:11 AM 5 5
411 Stop D 10:16:00 AM 10:16 AM 10:34 AM
411 Stop D 10:16:00 AM 10:16 AM 10:21 AM -5 5
411 Stop D 10:16:00 AM 10:16 AM 10:11 AM 5 5
411 Stop D 10:16:00 AM 10:16 AM 10:01 AM 15 15
411 Stop D 10:16:00 AM 10:16 AM 10:06 AM 10 10
411 Stop E 11:16:00 AM 11:16 AM 11:00 AM 16 16
411 Stop E 11:16:00 AM 11:16 AM 11:01 AM 15 15
411 Stop E 11:16:00 AM 11:16 AM 11:12 AM 4 4
411 Stop E 11:16:00 AM 11:16 AM 11:03 AM 13 13
411 Stop E 11:16:00 AM 11:16 AM 10:56 AM 20 20
411 Stop E 11:16:00 AM 11:16 AM 11:23 AM
 
Upvote 0
Cosmic Wizard said:
Which part is not working?
Can't seem to upload the spreadsheet so will do so below.
It is the array formula?
Will go through all the steps just in case.

1. In Column A paste all the bus stops individual times, your Landmark Column, see below
2. Format Columns B and C with a custom format of h:mm AM/PM, paste the Scheduled Time and Actual Time
3. In Column D, headed Difference, paste this formula =IF(OR((B2-C2)*60*24>=30,(B2-C2)*60*24<5),"",(B2-C2)*60*24), copy down.
4. In Column E, headed No Negative, paste this formula =IF(D2="","",ABS(D2)): this removes any negative number which would distort finding the closest time

This then provides us in Column E with how much time a bus was either 30 mins early or 5 mins past the designate stop time. It returns a blank cell if the time is outside those two parameters.

The next step in a group of cells is to paste the various Bus Stop times, remove the duplicates times, so you just have bus stops times like below; this will form your data validation list.

411 Stop C 9:16:00 AM
411 Stop D 10:16:00 AM
411 Stop E 11:16:00 AM


Form your data validation list, with in this case three examples.

Next use the array formula in the cell, {=MIN(IF(A2:A25=G12,E2:E25))}, remembering to press Control, Shift, Enter.

This formula looks up all the Bus Stop time in Column A A2:A25, the data validation list is in G12, so which ever time you choose from the drop down, say 411 Stop C 9:16:00 AM, that will be the time it looks up, and finds the minimum value available in Column E.

If the columns are in another sheet, you will need to include the sheet name like this {=MIN(IF(Sheet5!A2:A25=Sheet6!I3,Sheet5!E2:E25))}, again with Control, Shift, Enter.

Hope this make sense: if not post again: this so works!


Bus Stop Scheduled Time Actual Time Difference No Negative
411 Stop C 9:16:00 AM 9:16 AM 9:06 AM 10 10
411 Stop C 9:16:00 AM 9:16 AM 9:16 AM 0 0
411 Stop C 9:16:00 AM 9:16 AM 9:11 AM 5 5
411 Stop C 9:16:00 AM 9:16 AM 9:17 AM -1 1
411 Stop C 9:16:00 AM 9:16 AM 10:16 AM
411 Stop C 9:16:00 AM 9:16 AM 9:46 AM
411 Stop C 9:16:00 AM 9:16 AM 9:46 AM
411 Stop C 9:16:00 AM 9:16 AM 9:23 AM
411 Stop C 9:16:00 AM 9:16 AM 9:34 AM
411 Stop C 9:16:00 AM 9:16 AM 9:32 AM
411 Stop C 9:16:00 AM 9:16 AM 9:03 AM 13 13
411 Stop C 9:16:00 AM 9:16 AM 9:06 AM 10 10
411 Stop D 10:16:00 AM 10:16 AM 10:11 AM 5 5
411 Stop D 10:16:00 AM 10:16 AM 10:34 AM
411 Stop D 10:16:00 AM 10:16 AM 10:21 AM -5 5
411 Stop D 10:16:00 AM 10:16 AM 10:11 AM 5 5
411 Stop D 10:16:00 AM 10:16 AM 10:01 AM 15 15
411 Stop D 10:16:00 AM 10:16 AM 10:06 AM 10 10
411 Stop E 11:16:00 AM 11:16 AM 11:00 AM 16 16
411 Stop E 11:16:00 AM 11:16 AM 11:01 AM 15 15
411 Stop E 11:16:00 AM 11:16 AM 11:12 AM 4 4
411 Stop E 11:16:00 AM 11:16 AM 11:03 AM 13 13
411 Stop E 11:16:00 AM 11:16 AM 10:56 AM 20 20
411 Stop E 11:16:00 AM 11:16 AM 11:23 AM
Click to expand...



I am so sorry, but it seems that the fundamental issue of trying to identify what time the bus actually made the stop time is not being accounted for. Above on #2 you have listed paste "Scheduled Time and Actual Time". The issue is that I would like for the formula to find the actual time as closest to the scheduled time. I do not want to have to manually paste each actual time.

Here is the spreadsheet that I am using and my columns do not match up with what you have listed.

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

Could you upload your spreadsheet via google drive and share it?

I really appreciate all of your help!
 
Upvote 0
RyanHillAZ said:
I am so sorry, but it seems that the fundamental issue of trying to identify what time the bus actually made the stop time is not being accounted for. Above on #2 you have listed paste "Scheduled Time and Actual Time". The issue is that I would like for the formula to find the actual time as closest to the scheduled time. I do not want to have to manually paste each actual time.

Here is the spreadsheet that I am using and my columns do not match up with what you have listed.

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

Could you upload your spreadsheet via google drive and share it?

I really appreciate all of your help!
Click to expand...


Any chance you can upload your spreadsheet example?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Column Output 2
Replies
7
Views
203
vmjan02
V
J
  • Solved
INDEX MATCH Function Help
Replies
6
Views
235
jeffj13
J
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
332
KRice
K
K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
RichTom44
  • Question
How to highlight a certain time range
Replies
3
Views
303
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top