# Thread: Need to compare Latitude & Longitude coordinates to find closest office Thanks: 0 Likes: 0

1. ## Re: Need to compare Latitude & Longitude coordinates to find closest office

i am at a loss to why this fomula is not working. i read previously about how to invoke the formula. if I use the original formula =INDEX(\$A\$3:\$A\$7,MATCH(SMALL((ABS(J3-\$E\$3:\$E\$7)^2+ABS(K3-\$F\$3:\$F\$7)^2)^(0.5),1),(ABS(J3-\$E\$3:\$E\$7)^2+ABS(K3-\$F\$3:\$F\$7)^2)^(0.5),0)) with control **** enter i have no problem.

if i correct the formula for my dataset
=INDEX(\$A\$3:\$A\$1200,MATCH(SMALL((ABS(J3-\$E\$3:\$E\$1200)^2+ABS(K3-\$F\$3:\$F\$1200)^2)^(0.5),1),(ABS(J3-\$E\$3:\$E\$1200)^2+ABS(K3-\$F\$3:\$F\$1200)^2)^(0.5),0)) i get a value error.

all i changed to the 7's to 1200 to account for my dataset.
I am grateful for any other thoughts or ideas on this.

2. ## Re: Need to compare Latitude & Longitude coordinates to find closest office

I found the problem. I had a hole in my dataset.

3. ## Re: Need to compare Latitude & Longitude coordinates to find closest office

This is fantastic. It works great for me. I have one question though. How could I adapt this formula to find the nth closest affiliate to a given lat/long? (I really just want to find the top 5)

Thanks!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•