Excel Formula to Find the Nearest City Using Latitude and Longitude

Mapping raw GPS coordinates to the nearest city in Excel is notoriously tedious and resource-intensive. While developers often rely on expensive GIS software or complex API integrations to bridge this gap, business analysts require a native, cost-effective alternative. Utilizing localized lookup formulas grants organizations immediate, automated spatial insights without external subscription costs.

As a stipulation, the precision of this approach depends entirely on the density of your reference database. For example, matching coordinates like (40.7128, -74.0060) to "New York" requires a structured master list. Below, we will detail the exact INDEX, MATCH, and trigonometric formulas required to build this coordinate-matching engine.

Managing geographic data in Excel is a common task for logistics coordinators, sales analysts, and digital marketers. Often, you are handed a list of raw GPS coordinates (latitudes and longitudes) and tasked with assigning each point to the nearest physical branch, warehouse, or major city.

Because Excel is not a dedicated Geographic Information System (GIS) tool, it does not have a native =NEAREST_CITY() function. However, by combining mathematical principles like the Pythagorean theorem or the Spherical Law of Cosines with Excel's powerful lookup engines (like XLOOKUP and LET), you can build a highly accurate, dynamic geocoding matching engine directly in your spreadsheets.

In this guide, we will walk through how to match coordinates to the nearest city using both a simplified localized formula and a globally accurate spherical formula.

The Architectural Setup: Organizing Your Data

Before writing formulas, you must organize your workbook. You need two distinct tables: your target coordinates list (the points you want to analyze) and a reference lookup table containing the coordinates of known cities.

1. The Target Coordinates Table

This table contains the raw GPS data you want to resolve. For this guide, let us assume this table is named DataLocations.

2. The Reference Cities Table

This is your reference database. It must contain the names of target cities along with their precise coordinates. Let us assume this table is named CitiesMaster.

Method 1: The Modern Office 365 Method (Highly Recommended)

If you are using Office 365 or Excel 2021+, you have access to dynamic arrays, LET, and XLOOKUP. This makes building a spherical math formula clean and readable.

To accurately calculate distances on a curved earth, we use the Spherical Law of Cosines. This formula calculates the great-circle distance between two points. While slightly less precise than the Haversine formula in edge-case situations, the Law of Cosines is significantly shorter and performs much faster in Excel calculations.

The Formula

Enter the following formula in cell D2 of your Target Coordinates Table:

=LET(
    target_lat, B2,
    target_lon, C2,
    city_lat, CitiesMaster[Latitude],
    city_lon, CitiesMaster[Longitude],
    city_name, CitiesMaster[City Name],
    
    distances, ACOS(
        SIN(RADIANS(target_lat)) * SIN(RADIANS(city_lat)) + 
        COS(RADIANS(target_lat)) * COS(RADIANS(city_lat)) * 
        COS(RADIANS(city_lon) - RADIANS(target_lon))
    ) * 6371,
    
    XLOOKUP(MIN(distances), distances, city_name)
)

How It Works

• LET Function: We define variables (like target_lat and city_lat) to keep our calculation organized and prevent Excel from parsing complex ranges multiple times.
• RADIANS Conversion: Excel's trigonometric functions (SIN, COS, ACOS) only accept inputs in radians. We use RADIANS() to convert decimal degrees.
• Spherical Calculation: The ACOS(...) * 6371 block processes the physical distance between the target coordinates and every single row in the CitiesMaster table simultaneously, outputting an array of distances in kilometers (change 6371 to 3959 if you prefer miles).
• XLOOKUP & MIN: MIN(distances) finds the smallest distance in that calculated array. XLOOKUP looks up that minimum value within the distances array and returns the corresponding city name from the city_name array.

Method 2: The Fast Local Approximation (Euclidean Distance)

If your dataset spans a relatively small geographic area (such as a single state or metropolitan region) and doesn't cross the international date line, you can bypass complex trigonometric math. Instead, you can calculate the flat-plane distance using the Pythagorean Theorem: a² + b² = c².

This method runs significantly faster on large datasets because it avoids heavy trigonometric transformations.

The Formula

=LET(
    t_lat, B2,
    t_lon, C2,
    c_lat, CitiesMaster[Latitude],
    c_lon, CitiesMaster[Longitude],
    
    hypotenuse, ((c_lat - t_lat)^2) + ((c_lon - t_lon)^2),
    
    XLOOKUP(MIN(hypotenuse), hypotenuse, CitiesMaster[City Name])
)

Note: This does not give you the physical distance in miles or kilometers, but it successfully identifies the nearest point by comparing relative spatial offsets.

Method 3: Legacy Excel Version (Excel 2019 and Older)

If you are working on an older version of Excel, you cannot use the LET function or direct array lookups within XLOOKUP. Instead, you must combine INDEX, MATCH, and MIN, entering it as a legacy Array Formula.

The Formula

Paste the following formula into cell D2, and instead of pressing Enter, press Ctrl + Shift + Enter (CSE). Excel will wrap the formula in curly braces { } to indicate it is evaluating arrays.

=INDEX(CitiesMaster[City Name], MATCH(MIN(ACOS(SIN(RADIANS(B2)) * SIN(RADIANS(CitiesMaster[Latitude])) + COS(RADIANS(B2)) * COS(RADIANS(CitiesMaster[Latitude])) * COS(RADIANS(CitiesMaster[Longitude]) - RADIANS(C2))) * 6371), ACOS(SIN(RADIANS(B2)) * SIN(RADIANS(CitiesMaster[Latitude])) + COS(RADIANS(B2)) * COS(RADIANS(CitiesMaster[Latitude])) * COS(RADIANS(CitiesMaster[Longitude]) - RADIANS(C2))) * 6371, 0))

Because Excel calculates this formula twice (once to find the MIN distance and once inside MATCH to find its position), this method can severely bottleneck performance if your workbook contains tens of thousands of rows.

Optimization Tips for Massive Datasets

If you are matching 50,000 target locations against 1,000 cities, Excel has to perform 50,000 × 1,000 = 50 million calculations. This can freeze your computer. Use these tips to optimize calculation speeds:

1. Limit Your Reference Cities Table: Do not use a database of every global city if you are only matching locations within Texas. Filter your reference table down to only the necessary municipal markers.
2. Implement a Bounding Box: If performance is lagging, you can use Power Query to filter out reference cities that are further than 1 degree of latitude/longitude away before computing the exact mathematical distances.
3. Convert Formulas to Values: Once the formulas have calculated the nearest cities, copy the column and paste it as Values (Alt + E + S + V). This locks in your results and prevents Excel from recalculating millions of trigonometric formulas every time you edit a cell.

Summary

By leveraging Excel's trigonometric functions combined with LET and XLOOKUP, you don't need expensive geocoding software to match coordinates to the nearest municipality. Utilize the Spherical Law of Cosines for global databases, or stick with the Pythagorean approximation for fast, localized mapping tasks.