Try to find the closest of two postcode lists

**KARLA X** · 05-02-2025, 08:03 AM

Hello Clever People!!
I have sample data where I have a list of sites that we work on (postcodes only) and a list of Hospitals with A&E departments (postcodes only). To cut a very long and painful story short, to work on a site we need to provide a big pack up of information that includes the nearest A&E department. So I have gathered the information but I have no idea how to identify which hospital postcode is closest to which site.

Is there any way around this? I know excel has to use latitudes and longitudes and have seen an example given previously. I'm just trying to avoid going through every possible combination between the two lists.

Would be very grateful for any help available.

Thank you in advance.

**daddylonglegs** · 05-02-2025, 10:21 AM

I used this link to convert some of your postcodes (I can't vouch for it's accuracy)

https://www.freemaptools.com/convert...to-lat-lng.htm

Then I used a version of the formula I found here:

https://stackoverflow.com/questions/...nates-in-excel

So see the attached where the formula in E4 copied down is

=LOOKUP(1,1/FREQUENCY(0,MMULT((H$4:I$16-C4:D4)^2,{1;1})),F$4:F$16)

[you'll have to extend the ranges in the formula once you have more conversions]

Because I only converted hospitals in the Birmingham postcodes they are all roughly equal distances, but obviously if you convert all of the data you will be able to do some sort of "sanity check" to see if the results make sense

**KARLA X** · 05-02-2025, 10:54 AM

Thank you so much, I will have a look. Hopefully that will work.

Try to find the closest of two postcode lists

LinkBack

Thread Tools

Rate This Thread

Display

Try to find the closest of two postcode lists

Re: Try to find the closest of two postcode lists

Re: Try to find the closest of two postcode lists

Thread Information

Users Browsing this Thread

Similar Threads

Find / Extract UK Postcode Using VBA

Finding the closest match from 3 corresponding lists

[SOLVED] Postcode check and return of value in column title and cell adjacent to postcode

I want the the drop down lists to start with the closest entry as the user types.

[SOLVED] Compare two lists of words to find closest matches

[SOLVED] Help needed trimming full postcode address to postcode sector.

2 lists of coordinates. I wanna find the closest

Bookmarks

Bookmarks

Posting Permissions