Back again,

Tried a few ways at doing this but need help, using Lat and Long coordinates I would like to find the closest (lets just call for example) "well"

I have attached a sheet, the first tab needs to show the closest beside each location.



This shows two separate columns, one Lat and the other Long, then using the "well" tab, I would like to find the closest one to each Location.

Am sure there must be a way, I just cant get my head around it, have tried doing matches, Chooses, Lookup functions but not having much luck.


Any help would be greatly appreciated.

Thanks very much

Steve

Attached Files

Help.xlsx (20.6 KB, 94 views)

Let me get this straight...for each set of coordinates on the first tab, you would like to look up the well on the second tab that is closest?

That is the what I am hoping to achieve, I feel this is impossible so am just having to do this manually on maps. Very time consuming.

Hoo boy...I bet this can be done with code. Perhaps try the Excel programming forum just below this. My coding abilities are rusty as hell, but I bet there's someone there that can steer you right.

So definitely no formulae / calculation. My coding abilities are close to non existent, so I think will just settle with having to use a map app to manually do it. just wanted to make my job at work easier.

I'll try the programming forum they may have some ideas.

Thank you for getting back to me.

I don't know. It might be possible with some array functions, but that is again above my pay grade.

I googled "lookup comparing coordinates excel" and there were a lot of articles, some going back as far as 2002. There were a few promising solutions, but they all required code e.g. http://www.excelbanter.com/showthread.php?t=121991

Thanks very much anyway,

Try the following array formula:
Note: You'll need to delete any spaces inserted into the formula by the forum software.

The approximate minimum distance (in km) is given by the array formula:

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Bravo, Paul. Bravo!!!!

Wow! Thanks very much, I don't understand a single part of it, but it works!

Thanks again.

Steve

FWIW, the standard formula for calculating the distance between two lat/lon coordinates is:
That's the basic formula my solution uses. It's not especially accurate at very small differences, particularly near the poles, but that's of no consequence for this exercise, and any errors will still scale according to the differences in the true distances. Without the '*RadiusEarth' part (6367.465km on average), you simply get a value applicable to any size sphere. Indeed, the above formula all there is to the second one in post #8, except that one also uses the MIN function in an array formula to return the minimum distance.

The MATCH function simply uses two of these formulae (without the '*RadiusEarth' part), first with the MIN function to calculate a minimum spherical value, the second without the MIN function to find which row produces that value. The INDEX function merely outputs the corresponding name from the row returned by the MATCH function in column C.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello,

Can you post the final Excel file with the working functions? I'm having trouble replicating in my file.

Thanks,
Brian

Hi Brian.

Am trying to look for it, but I either did a copy and paste special on the final sheet, or found that in the end might have just used Google Earth. It was a while ago now and i cannot remember for the life of me.

Sorry


If I do find I will post up on here for you..


Steve

See attached.

Attached Files

Demo.xlsx (32.8 KB, 234 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Sorry to bump a 3 year old thread, however this gives me exactly what I need however when I try to expand the number of "Wells" being matched from 151 to 766 I return #N/A values. How would edit this code to look up against all 766 wells?

All help is greatly appreciated

Regards