What I have:

A workbook with two worksheets (three, actually, but the third won't come into play until later). One worksheet has data taken from flight opertations log, the other worksheet contains imported hourly weather data.


What I need:

I need to fill in the weather data that most closely matches the times when the flights were observed. For example - I have data for an aircraft that departed on a certain runway at a specific time. I would like to automatically import a few columns of weather data closest to the aircraft departure time. I believe this might be accomplished with a combination IF statement and the use of the Indirect command, butI am unclear as to how to structure the statement.


How the Data is Being Used:

Ultimately the data will be used to determine if the aircraft at our airport are utlizing the preferential runway program (taking off to and landing from the north) to reduce noise levels in sensitive areas to the south of the airport. When winds are strong enough, the aircraft have no choice but to take off to or approach from the south for landing, but I believe that I can handle that information using conditional format statements.

I've attached a compressed copy of the spreadsheet to help make things clearer.

Any help would be greatly appreciated!

you will need a formula like below, entered as an array formula

=INDIRECT(ADDRESS(ROW(targetrange)+MATCH(MIN(ABS(d ata-targetrange)),ABS(data-targetrange),0)-1,COLUMN(targetrange)+colmoffset,,,"September weather"))where targetrange is your matching data
colmoffset is the offset from the targetrange to the data required
data is the the address of the cell containing the time you are seeking

I named c2:c27 on September weather as time and ran the formula below to pull in a value from col J to H11 on September Acft. Remember to use shift+ctrl+enter to enter as an array formula


=INDIRECT(ADDRESS(ROW(time)+MATCH(MIN(ABS(E11-time)),ABS(E11-time),0)-1,COLUMN(time)+7,,,"September weather"))


Brian

Oh yes I forgot the time formats are different on each sheet, I changed them on sheet 2 which made a mess of col D, also you how are you handling the date?

brian

Oh yes I forgot the time formats are different on each sheet, I changed them on sheet 2 which made a mess of col D, also you how are you handling the date?

brian


Brian,

You beat me to the punch :-) I do indeed need to take the date into account as well, and it would also seem more appropriate to use the time data that has been converted from text into an actual number. Your formula looks very promising and I apprciate your time and effort to help me out on this.

John

Can you not have one field containing date and time on each sheet as I think this would avoid complications. Yes it is possible to do two column lookups by concatenating ranges tho' I have never done it, and I'm not sure about building it into the formula I supplied.

Brian

You're right, there's no reason that the two columns couldn't be combined. I could set up a seperate function to taake the two and combine them into a third.

John

So it can't be ebtered as date and time initially?

Got to go now will look in tomorrow.

Brian

I'm trying to take advantage of existing logs without requiring too many changes for the operations staff, so I combined the date and time on each sheet and the formula works beautifully. Thank you very much for your help. I only had a vague idea of how it might be done before you showed me.

John