Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I have a huge (24k+) number of waypoints given latitude and longitude in a table. On another sheet i have a large (1k+) number of other points on or near the track. In fact most are just off the track so very few of the lat/longs match the first list.
What I need to do is something like a lookup function but because they don't match, I need them to return which point is [u]nearest [/u]to the query-point.
Anyone any ideas?
C
Vlookup will provide the closest match as long as you don't include the False attribute at the end of the formula.
eg.
vlookup(a1,b1:c100,2, false) will find an exact match to A1.
vlookup(a1, B1:c100, 2, true) will find the closest value to A1
vlookup(a1, B1:c100, 2) will find the closest value to A1
Careful, VLOOKUP won't give you the nearest it will give you
If an exact match is not found, the next largest value that is less than lookup_value is returned
Handy for most factor lookups I do but not exactly what you asked for
(provided you have already taken Perchy's comments on true/false on board)
I'm no expert at all so prodding and floundering but I'll see if i can get something to work. Buccaneer - If all else fails can I send you the sheet and see if it's easy?
it is mountain bike related!
C
You could but I haven't actually worked out how to give you what you want 🙂
Especially as the coordinates contain two dimensions, it all gets to be a bit of a brain ache 🙂
Maybe use a bit of Pythagoras to calculate the distance between the coordinates and pick the minimum value
yes I'm just wondering if i can make up two new sheets and make a table with all the differences between each points lat and long then find minimums. it will be huge.
As you've got geographic coordinates, why not use a GIS? You could download QGIS (it's free) and then follow these instructions - http://www.qgistutorials.com/en/docs/nearest_neighbor_analysis.html
Alternatively, email me the spreadsheet and I can do it for you (not till this evening though).
Greg
Careful, VLOOKUP won't give you the nearest it will give youIf an exact match is not found, the next largest value that is less than lookup_value is returned
Handy for most factor lookups I do but not exactly what you asked for(provided you have already taken Perchy's comments on true/false on board)
Not only that, but it just gets to the first value it finds which is equal to or higher than your lookup (and goes back one if higher) so they need to be in ascending order (and I would expect that's unlikely to be consistently the case for both lat and long). So if you're looking for 5.5 (say) and your list is 1, 3, 8, 6, 5.5, vlookup (or hlookup) with 'false' on the end would return 3.
Excel is good at many things but handling spatial data is not one of them I'm afraid. Some sort of GIS might be easier although what I normally work in (Petrel for O&G) would need some thought to do this.
If it was just E or N that was required this sort of makes sense, but I can't see a simple was of finding the nearest 'pair' to another 'pair', other than a load of faff with pythagoras, index match and the like.
One thought was to plot the data as two sets of x/y and have a look at the offeset in the resulting graph.
A better idea (as above) is to get a GIS.
Greg +1
Use the right tool for the job.
+1 for using the right tool..
however try
(the following is an array formula and needs to be entered Holding control shift enter) when you look at the formula it will have {} at each end
=INDEX($C$1:$C$17,MATCH(SMALL(((($D$1:$D$17)-A1)^2+(($E$1:$E$17)-B1)^2)^0.5,1),((($D$1:$D$17)-A1)^2+(($E$1:$E$17)-B1)^2)^0.5,0))
where
A1 - B17 is the track lat and long
C1 - C17 is the waypoint ref
D1 - E17 is the way point lat and long
Power bi now has geo spatial stuff in it and designed to do some stuff like this. Not sure how though but it's free and clever