Find date/time that is between dates/times in another dataset

[MagiCarty]

So I have two sets of data that I need to match up.

Set 1:
Column A has "Vehicles" like 29, 21, 20, etc. Column B has "Date/time" of when that vehicle last sent data.

Set 2:
Column D has the "Arrival Date/Time" of when the vehicle entered a location. Column E has the "Departure Date/Time" of when the vehicle left the location. Column F is the "Location" where the vehicle was between the Arrival and Departure times. Column G has the "Vehicle" assignment again (29, 21, 20, etc).

I'm looking for a formula to tell me which Location the vehicle was at by using the date/time in column B to be between the date/times in columns D and E, AND to match the vehicle numbers.

I can provide actual data if this will help. I wish there was an easy way to embed actual excel data into these things. Many thanks for your helps.

 

[cyrilbrd]

So I have two sets of data that I need to match up.

Set 1:
Column A has "Vehicles" like 29, 21, 20, etc. Column B has "Date/time" of when that vehicle last sent data.

Set 2:
Column D has the "Arrival Date/Time" of when the vehicle entered a location. Column E has the "Departure Date/Time" of when the vehicle left the location. Column F is the "Location" where the vehicle was between the Arrival and Departure times. Column G has the "Vehicle" assignment again (29, 21, 20, etc).

I'm looking for a formula to tell me which Location the vehicle was at by using the date/time in column B to be between the date/times in columns D and E, AND to match the vehicle numbers.

I can provide actual data if this will help. I wish there was an easy way to embed actual excel data into these things. Many thanks for your helps.

Hi,

Given in A1:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl64 { text-align: center; }.xl65 { text-align: center; }.xl66 { text-align: center; }</style>

vehicles	DT	location is	A DT	D DT	location	vehicle
29	1/1/13 6:10	X	1/1/13 6:00	1/1/13 6:30	X	29
30	1/1/13 7:00	W	1/1/13 7:20	1/1/13 7:40	Y	30
31	1/1/13 8:00		1/1/13 8:30	1/1/13 8:38	Z	31
			1/1/13 6:50	1/1/13 7:05	W	30

<tbody>
</tbody>

Formula in C2 is =IFERROR(INDEX(Sheet3!$F$2:$F$5,MATCH(1,(B2 > Sheet3!$D$2:$D$5)*(B2 < Sheet3!$E$2:$E$5)*(A2=Sheet3!$G$2:$G$5),0)),"")
Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC.
<s heet3!$e$2:$e$5)*(a2="Sheet3!$G$2:$G$5),0)),"")

</s>

 

[MagiCarty]

My customer's data source is flawed, however this formula works perfectly. Thank you.

 

[cyrilbrd]

My customer's data source is flawed, however this formula works perfectly. Thank you.

Glad it worked for you.
Thanks for the feedback and good luck for cleaning up the database!