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

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!

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?

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.

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 <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd>:

=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.