Copying/Creating a Zipcode (PostCode) distance checker assistance

Hello all,


I was given a postcode checker for the UK. There is a worksheet on the file for all the post codes along with latitude and longitude and I've managed to duplicate this list for the US with the same format. I found a zip code to latitude and replicated the format for the US section that I want to copy over.

When I attempt to copy over the formulas to calculate the distance and reference the respective locational data, it doesn't work and it presents me with #NA.

I've attached the list of sample zip/post codes with latitude and longitude.

When I try to get 'Lat_2' and 'Long_2' to work in the sheet "Multiple US", it comes up as blank for 'Lat_2' and #NA for 'Long_2.' The UK Sheets are working perfectly and I'm not quite sure what I'm doing wrong!

I think I posted that file quite some time ago.

The problem is that your values in column B of the Multiple_US sheet are numeric, whereas in the NA_Postcodes sheet they are text values. You can change the following formulae as shown (in red):

E4: =IF(B4&""="","",IF(ISNA(VLOOKUP(B4&"",NA_PostCodes!A$2:D$11,1,0)),"",VLOOKUP(B4&"",NA_PostCodes!A$2:D$11,3,0)))

F4: =IF(B4&""="","",VLOOKUP(B4&"",NA_PostCodes!A$2:D$11,4,0))

The &"" will convert the column B values to text values, so you can carry on using numbers if you wish. Obviously, copy the formulae down as required.

Hope this helps.

Pete

Thanks Pete_UK!

That's sorted it out, I can't believe I've ran into the author of the file that I was passed along!

Glad to help, and thanks for the rep.

If you are interested, here is a link to the original thread:

https://www.excelforum.com/excel-tip...alculator.html

Hope this helps.

Pete