find the closest match to given latitude / longitude from data stored in 2 columns

Greetings,

I've run into a wall with this one, so I had to ask for help

I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.

After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works!
However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set. This doesn't work, and I don't have a clue how to go about it.

Any good Samaritans out there? Please?
File attached with sample data sets.

Many thanks,
Florin

Hi Martin,

Thanks for your reply.
I knew about Haversine or spherical cos law, but do you suggest doing it about 1000 times for all cities in the data set?
Isn't it going to be slow?

Florin

Matching the closest pair of coordinates from the cities data set?

Not sure about what your trying to set in B10

Thanks bsalv, I'll check it out and let you know how it works.

Nothing, this was just a debug output for one of my attempts.

Hi Gerard, thanks for your try but your solution matches only the closest latitude while ignoring the longitude; it means you get the wrong city.

Hi bsalv,
I've tried to run your macro for all my 5700 stations but got the run-time error '9' subscript out of range after 747 stations at the line >> If MinCity = "" Then MinCity = c(i1, 3).
Any suggestions?

Greetings,

Let me summarise and give some thanks where due, before closing this thread.
What I had in mind was a run-time solution for finding the closest city similar with Gerard's attempt, i.e. using match, index, offset, etc.; that's why I initially questioned mrice's proposal for computing the distance.
What mrice, shg and bsalv proposed was a data set revamp, a new data set containing all info including the stations-cities closest match. On a balance I accept that this is the right solution and thank you all for helping. Reputation added.
Have a good day.

Hi shg, special thanks due to you for your very neat and fast distance calculation code.

Oops! Sorry to pester shg, but I'm getting the wrong results when running your code for all my stations; see example in the attached jpg, the highlighted station is very close to Melbourne, Australia.
Could you help please?

Time to wash my face; got egg all over it!
Forgotten to add my full list o cities - in your file was only a sample. Apology!

Originally Posted by florin_excel

Time to wash my face; got egg all over it!
Forgotten to add my full list o cities - in your file was only a sample. Apology!

Wondering why it returns the last data sets?