# Postcode to postcode distance checker

1. ## Postcode to postcode distance checker

Hi All,

I have 40,000 postcodes that I need to compare against 22 postcodes to see which of the 22 is the nearest in distance to each of the 40k.

I have a list of all the longitude and latitude codes for each of the 40,000 postcodes but i now need to know how to formulate a function in excel - if that is even possible.

I am thinking that if i link to the internet and perform 900k look ups it will take forever/crash the system.

Any ideas?

Kerry

2. ## Re: Postcode to postcode distance checker

Hi and welcome to the forum!

Assuming that the Latitude and Longitude for the first of your 40,000 for which you wish to obtain a result are in B2 and C2 respectively, try this array formula**:

=INDEX(PostCode,MATCH(SMALL((SQRT(ABS(B2-Latitude)^2+ABS(C2-Longitude)^2)),1),SQRT(ABS(B2-Latitude)^2+ABS(C2-Longitude)^2),0))

where you should replace PostCode, Latitude and Longitude with the appropriate ranges pertaining to the 22 in your actual set-up (e.g. replace PostCode by \$X\$2:\$X\$23, etc.).

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1