Selecting the nearest static location (weather station) for a list of GPS tracker points

Hi there. First time poster here, hoping someone can help!

I'm working with large datasets of GPS tracks (latitude & longitude) and I am having to calculate the distance (km) between each location and the previous point in the track, which I've done using the following formula:

Formula:

Please Login or Register  to view this content.

Where column O is latitude and P is longitude and this seems to work just fine.

I now need to:

1. also calculate distances between each location from the GPS tracks and a few static locations (weather stations), which I have in a separate table; AND
2. select the closest weather station and display the associated weather variables (min/max temp, wind speed/direction, rain, etc.) for that particular date/time from the table.

I'm sure there must be a neat way of doing this with a single formula, but this is where I'm stuck!

I can provide sample data if necessary but I've basically got lists of latitude and longitude for the GPS tracks at certain time/date intervals and separate lists of hourly weather data at fixed locations.


Any help would be very appreciated, thanks!

It would help if you attached a sample Excel workbook which shows your data layout and in which you can show manually what you want to achieve.

To do this, follow the instructions in the yellow banner near the top of the screen.

Pete

Hi Pete

I'm attaching the sample workbook as requested.

The first sheet contains the GPS tracking data and I want the cells G3:Q12 to be filled using that data and the weather data from the other sheets.
G3 should show the name of the nearest weather station to C3:D3. Then based on that and the date/time the relevant weather data should then be displayed in the subsequent columns.

I hope that's a little clearer with the attached file.


Thanks again,
Ty

Originally Posted by HansDouwe

One way:

Create a sheet with the coordinates of the Weather stations and try in G3 and copy down:

Formula:

Please Login or Register  to view this content.

try in H3 and copy down:

Formula:

Please Login or Register  to view this content.

try in I3 and copy down:

Formula:

Please Login or Register  to view this content.

try in J3 and copy down:

Formula:

Please Login or Register  to view this content.

Hi Hans

This is very helpful as it clearly works!
However, would you mind adding an explanation as to how the formulas in G3 and J3 work please, as I am not familiar with the INDEX, INDIRECT and MATCH functions?


Many thanks again!

Could someone help by explaining this please?


Thanks

Thanks a lot