Closest air port codes to list of zip codes

[gdamato]

Hi,

Does anyone know of a formula of some kind that will tell me what the closest airport code is to a list of zip codes that I have? Let's say column A is a zip code, I would like column B to show JFK or ORD or any whatever airport code is closest. Bonus if column C can show the distance.

Thanks!

 

[cyrilbrd]

Given in A1:

Excel 2013
	A	B	C	D	E	F	G	H	I
1	city	country	IATA	ICAO	latitude	longitude	distance in KM		6371
2	New York	United States	JFK	KJFK	40.639751	-73.778925	5833.655549
3	Paris	France	CDG	LFPG	49.012779	2.55
Sheet2

Cell Formulas
Range	Formula
G2	=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(E2)-RADIANS(E3))/2)^2)+COS(RADIANS(E2))*COS(RADIANS(E3))*(SIN((RADIANS(F2)-RADIANS(F3))/2)^2)))))

Named Ranges
Name	Refers To	Cells
RadiusEarth	=Sheet2!$I$1

 

[gdamato]

Thank you for the response though I'm not really clear on how this will work. I'm have a list of us zip codes and would like to find the nearest airport (using an airport code like JFK, ORD etc...).

 

[cyrilbrd]

There is no mathematical correlation in between distance and zip code. Best would be to use latitude and longitude. Hence the creation of a table with latitude and longitude for each zip code.
Lat and long for commercial airports worldwide is public domain.