Nearest set of coordinates but excluding current coordinates and blanks from dataset

Question

My question builds off of the one asked here and the accepted answer:

Find the nearest set of coordinates in Excel

The accepted answer there provides two solutions:

1. Non-circular distance:

   =LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)
   

2. Circular distance:

   =LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(B$1:B$10-E1))/2)^2+SIN((RADIANS(C$1:C$10-F1))/2)^2*COS(RADIANS(B$1:B$10))*COS(RADIANS(E1))),A$1:A$10)
   

However, that dataset looks like this:

I'm trying to work with a dataset that looks like the following:

What I'm trying to do is:

• Fill in G and H where they are empty.
• If G and H is empty, look at E and F (e.g. G2 and H2 are empty, so get the coordinates from E2 and F2).
• Search all E and F for the next closest coordinates where G and H are not blank.
• Return the value of G and H.

All I have so far is modifying the Non-circular solution:

=LOOKUP(1,1/FREQUENCY(0,MMULT(($E$2:$F$69778-E2:F2)^2,{1;1})),$H$2:$H$69778)

Of course with this, it just matches to itself and G and H are blank so it returns nothing.

Any suggestions for how to implement this?

As a last resort I can always just copy to a separate tab all of the rows with values in G and H and do the lookup against that. Trying to implement it without doing that first however.

Answer 1

The original formula is a bit hard to understand but uses the behaviour of Frequency and Lookup in a very efficient way. It avoids the need to re-calculate the differences and their squares which would be needed if you used the more obvious method of finding the minimum squared difference and looking it up in a list of the squared differences.

Short answer - you could easily tweak the non-circular formula by adding a large number to the non-blank rows:

=LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1+999*(D$1:D$10<>""))^2,{1;1})),A$1:A$10)

This gives E where the original formula would have given D.

The same with the circular formula, I think I'm right in saying that the value of this version of the haversine formula can never exceed 1, so try:

=LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(B$1:B$10-E1))/2)^2+SIN((RADIANS(C$1:C$10-F1))/2)^2*COS(RADIANS(B$1:B$10))*COS(RADIANS(E1))+10*(D1:D10<>"")),A$1:A$10)

Notes

If I were doing this now and starting from scratch, I would probably use Let and Xlookup in Excel 365 to make it easier to read.

As has been mentioned in a comment, there should be a latitude correction to the first formula to make it approximately correct over short distances.