Lookup to find closest date and time using lookup table

[QVC]

I am trying to find out a way in which to match all inbound flights for DFW & SFO to the nearest outbound flights from the lookup table while returning the outbound  flt_nbr, flt_date flt_day and time.  I have attached the sample inbound and lookup table.  Any assistance would be greatly appreciated.

[NicoleJohnson · Accepted solution]

Try something like the attached! 

Basic philosophy is to join your Lookup Table to the Inbound data (by Arrival city = Departure city), calculate the time between flights, filter out anything leaving before the inbound flight arrives (time between flights is a negative number), and then choosing the outbound flight with the shortest amount of time between arrival time and departure time.

Hope that helps!

Cheers,

NJ

[QVC]

Thank you Nicole!!  This is exactly what I was looking for.

[QVC]

I have been testing the solution with different data and ran into a snag. Although the objectives is to find the closest outbound flight, the flight has to be at least 3 hrs. from the Arrive Time.  I made a few tweaks and found that outbound flights were being used more than once. 

Is there a way to modify the criteria such that it uses the first instance (outbound flight) only if the TimeBetweenFlights is >180 and the outbound flight has not been match with any other inbound flight?

I have uploaded the results I am getting as a result of the initial workflow below where the TimeBetweenFlights is 20.  I have also included the new sample files.

Results

Thanks in advance for the help!

[QVC]

If anyone could help me figure out how to match with items in the lookup table only once, that would be greatly appreciated.