How to Find Top 3 Nearest Location Given Lat Long in Excel?

Question

so I have 2 sheets in Excel named Sheet1 and Sheet2. The table structure simply like below:

Sheet1

Sheet2

I want to find 3 nearest location from Sheet1 to Sheet2. Example of expected output should be:

I already tried =LOOKUP(1,1/FREQUENCY(0,MMULT((Sheet2!B$2:C$5-Sheet1!B2:C2)^2,{1;1})), Sheet2!A$2:A$5) but it only gave me 1 nearest location only.

Excel version: Excel 365

Thanks in advance!

It will depend very much on the definition of the distance: are you using Pythagoras distance (sqrt((x2-x1)^2 + (y2-y1)^2), Manhattan distance (abs(x2-x1) + abs(y2-y1)) or some other distance definition? — Dominique, Feb 16 '23 at 07:23
@Harun24hr I've edited my post, I am using Excel 365 version — arnold_p, Feb 16 '23 at 07:30
@Dominique I simply use point to point distance, so it is straight line distance — arnold_p, Feb 16 '23 at 07:31
Use vlookup, but have 3 versions changing the lookup parameter a small amount. — Solar Mike, Feb 16 '23 at 08:13
So why can't you come up with two other "lookup_values" adjusted for your definition of "nearest"? — Solar Mike, Feb 16 '23 at 08:32
Or consider controlling the data table to exclude the closest range of targets etc — Solar Mike, Feb 16 '23 at 08:37

score 2 · Answer 1 · answered Feb 16 '23 at 10:04

Right, at risk of having a faulty calculation of distance, try:

Formula in E1:

=REDUCE("Nearest #"&{1,2,3},ROW(A2:A6),LAMBDA(x,y,VSTACK(x,TAKE(TOROW(SORTBY(A9:A13,ABS(INDEX(B:B,y)-B9:B13)+ABS(INDEX(C:C,y)-C9:C13))),,3))))

Using a calculated distance of ABS(lat1-lat2)+ABS(long1-long2) gave me the exact same results as trying to find the actual distance in, say miles, using other more intricate formulae like this one. Are you sure about your desired results?

score 0 · Answer 2 · answered Feb 16 '23 at 09:14

So an example of vlookup() being used to do a straight line interpolation between two X values to give the calculated Y value.

=VLOOKUP(A2,$A$5:$B$8,2,1)+((A2-(VLOOKUP(A2+5,$A$5:$B$8,1,1)-VLOOKUP(A2,$A$5:$B$8,1,1)))/(VLOOKUP(A2+5,$A$5:$B$8,1,1)-VLOOKUP(A2,$A$5:$B$8,1,1)))*(VLOOKUP(A2+5,$A$5:$B$8,2,1)-VLOOKUP(A2,$A$5:$B$8,2,1))

Used this to get temperatures, density etc from steam tables aka tables of the properties of water and air.

score 0 · Answer 3 · answered Feb 16 '23 at 11:13

I've used the Pythagoras distance to calculate nearest location using SUMPRODUCT and INDEX.

Sheet1:

Sheet2:

Notice I've added just a test column with Alpacca and a rank column just to check my output but you don't need them.

Array formula (you must introduce it pressing CTRL+SHIFT+ENTER:

=INDEX(Sheet2!$A$2:$A$6,SUMPRODUCT(--(SMALL (SQRT (($B2-Sheet2!$B$2:$B$6)^2+($C2-Sheet2!$C$2:$C$6)^2), RIGHT (D$1,1))= SQRT (($B2-Sheet2!$B$2:$B$6)^2+($C2-Sheet2!$C$2:$C$6)^2))*ROW(Sheet2!$A$2:$A$6))-1)

If, by any chance, 2 or more locations are exactly at the same distance, the formula won't work and will return wrong output.

How to Find Top 3 Nearest Location Given Lat Long in Excel?

3 Answers3