Forum Discussion
Look for dates/times that are closest to specified date/time
First, please keep all scheduled flights on the same worksheet. Doing otherwise would result in formulas that are more complicated and less efficient.
The solution in the attached workbook requires Excel 365 or Excel for the web.
Columns D cells will contain essentially the same formula (it can be copied down as needed), and the column J formulas are not much different. Read the _Info worksheet for more information.Here's the formula from cell D7:
=LET( desired_route_useful_times, FILTER(Sheet2!$B$2:$D$500, (Sheet2!$A$2:$A$500 = B7) * (Sheet2!$D$2:$D$500 <= C7) ), sorted_flights, SORT(desired_route_useful_times, 3, -1), best_flight, TAKE(sorted_flights, 1), IFERROR( best_flight, "" ) )I have used a maximum of about 500 rows. If you have more flight data than that, those cell references can be readily changed. Or better, consider replacing the range references with dynamic named ranges; unfortunately, they will be difficult to set up accurately if you include completely empty rows between each route, as in your sample data.
First, please keep all scheduled flights on the same worksheet. Doing otherwise would result in formulas that are more complicated and less efficient.
The solution in the attached workbook requires Excel 365 or Excel for the web.
Columns D cells will contain essentially the same formula (it can be copied down as needed), and the column J formulas are not much different. Read the _Info worksheet for more information.
Here's the formula from cell D7:
=LET( desired_route_useful_times, FILTER(Sheet2!$B$2:$D$500,
(Sheet2!$A$2:$A$500 = B7) * (Sheet2!$D$2:$D$500 <= C7) ),
sorted_flights, SORT(desired_route_useful_times, 3, -1),
best_flight, TAKE(sorted_flights, 1),
IFERROR( best_flight, "" )
)I have used a maximum of about 500 rows. If you have more flight data than that, those cell references can be readily changed. Or better, consider replacing the range references with dynamic named ranges; unfortunately, they will be difficult to set up accurately if you include completely empty rows between each route, as in your sample data.
SnowMan55 This looks to work perfectly! I'll do some more evaluating and reply if I see any errors. Thank you also for the explanations of the LET, FILTER, and TAKE commands-- these are all new ones to me!
Thank you so much!
