How to find the nearest location in Google Sheets

What you need

Two tabs with coordinates. Distance formulas need latitude and longitude, not addresses, so if you only have addresses, geocode them first (our geocoding guide covers the free and paid options).

• Stores tab: name in column A, latitude in B, longitude in C, starting in row 2. Give every location a unique name.
• Customers tab: name in A, latitude in B, longitude in C.

The examples use four stores (Seattle, Portland, Spokane and Boise) and rows 2:100 on the Stores tab. Change 100 to your last row. Empty rows inside that range are handled: they count as 100,000 miles away, so they’re never the nearest.

Nearest location and its distance

Put this in Customers!D2. It fills D2 with the nearest store’s name and E2 with the distance in miles:

=ARRAYFORMULA(LET(d, IF(Stores!$B$2:$B$100="", 100000, 2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!$B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS(Stores!$B$2:$B$100))*SIN(RADIANS(Stores!$C$2:$C$100-$C2)/2)^2))), {INDEX(SORTN(Stores!$A$2:$A$100, 1, 0, d, TRUE), 1), ROUND(MIN(d), 1)}))

Then copy D2 down the column (select D2 and the cells below it, and press Ctrl+D, or ⌘+D on a Mac). How it works: d is the haversine distance from this customer to every store, explained in our haversine guide; SORTN sorts the store names by d and keeps the first; MIN(d) is that distance. For kilometres, change 3958.8 to 6371.

With our four stores, a customer in Tacoma (47.2529, −122.4443) gets Seattle and 25 miles, and one in Salem, Oregon (44.9429, −123.0351) gets Portland and 43.2. We checked these numbers before publishing.

The three nearest

For a backup option, or to offer a customer a choice, list the three nearest with their distances in one cell:

=ARRAYFORMULA(LET(d, IF(Stores!$B$2:$B$100="", 100000, 2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!$B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS(Stores!$B$2:$B$100))*SIN(RADIANS(Stores!$C$2:$C$100-$C2)/2)^2))), TEXTJOIN(", ", TRUE, SORTN(Stores!$A$2:$A$100&" ("&ROUND(d, 1)&" mi)", 3, 0, d, TRUE))))

A customer in Yakima gets “Seattle (110.4 mi), Portland (128.4 mi), Spokane (162.1 mi)”. To put the three names in three separate cells instead, replace the TEXTJOIN(…) part with TRANSPOSE(SORTN(Stores!$A$2:$A$100, 3, 0, d, TRUE)). Change the 3 for more or fewer.

Everything within a radius

For “which reps cover this customer?” or “is anyone within 25 miles?”, filter instead of sorting. All locations within 25 miles, or “none”:

=ARRAYFORMULA(LET(d, IF(Stores!$B$2:$B$100="", 100000, 2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!$B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS(Stores!$B$2:$B$100))*SIN(RADIANS(Stores!$C$2:$C$100-$C2)/2)^2))), IFERROR(TEXTJOIN(", ", TRUE, FILTER(Stores!$A$2:$A$100, d<=25)), "none")))

How many locations are within 25 miles:

=ARRAYFORMULA(LET(d, IF(Stores!$B$2:$B$100="", 100000, 2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!$B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS(Stores!$B$2:$B$100))*SIN(RADIANS(Stores!$C$2:$C$100-$C2)/2)^2))), SUM(IF(d<=25, 1, 0))))

Put the radius in a cell, say Customers!$H$1, and use d<=$H$1 so you can try different radiuses without editing formulas.

Sort all locations from one point

The reverse question (“which of our stores are closest to this new warehouse?”) needs only one formula. Put the point’s latitude and longitude in Stores!F1 and G1, then in Stores!D2:

=ARRAYFORMULA(IF(B2:B="",, 2*3958.8*ASIN(SQRT(SIN(RADIANS(B2:B-$F$1)/2)^2+COS(RADIANS($F$1))*COS(RADIANS(B2:B))*SIN(RADIANS(C2:C-$G$1)/2)^2))))

That fills a distance for every store. To see them sorted without disturbing the tab, use =SORT(Stores!A2:D100, 4, TRUE) on another tab, or =FILTER(Stores!A2:D100, Stores!D2:D100<=50) for only those within 50 miles.

Nearest other store, or only open stores

Two variations come up often, and both work by changing which rows count as “100,000 miles away” before the formula picks the minimum.

Nearest other location in the same list. To find each store’s closest neighbour on the Stores tab itself, the plain formula would return the store itself at 0 miles. Add ROW(…)=ROW() to the empty-row test, so the formula’s own row is skipped. In Stores!E2, copied down:

=ARRAYFORMULA(LET(d, IF(($B$2:$B$100="")+(ROW($B$2:$B$100)=ROW()), 100000,
 2*3958.8*ASIN(SQRT(SIN(RADIANS($B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS($B$2:$B$100))*SIN(RADIANS($C$2:$C$100-$C2)/2)^2))),
 {INDEX(SORTN($A$2:$A$100, 1, 0, d, TRUE), 1), ROUND(MIN(d), 1)}))

On our four test stores it returns Seattle → Portland (145.4 miles), Portland → Seattle (145.4), Spokane → Seattle (228.4) and Boise → Spokane (285.6). It fills two columns, so E and F must be empty. The formula is for a list compared with itself; it uses the row number, so keep it on the same rows as the data.

Only stores that meet a condition. Add a Status column (D) to the Stores tab and add one more test, so closed stores are pushed out of reach. Use this in Customers!D2 in place of the first formula:

=ARRAYFORMULA(LET(d, IF((Stores!$B$2:$B$100="")+(Stores!$D$2:$D$100<>"Open"), 100000,
 2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!$B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS(Stores!$B$2:$B$100))*SIN(RADIANS(Stores!$C$2:$C$100-$C2)/2)^2))),
 {INDEX(SORTN(Stores!$A$2:$A$100, 1, 0, d, TRUE), 1), ROUND(MIN(d), 1)}))

With Seattle marked Closed, the Tacoma customer’s nearest store changes from Seattle (25.0 miles) to Portland (120.6 miles). The same pattern works for any filter: a store type, a region, or a stock flag. If every store is excluded, the distance shows 100000, which is your cue that nothing qualified.

Ties. When two stores are exactly the same distance away, SORTN with a ties mode of 0 returns just one of them: the one higher up the Stores list. Use the three-nearest formula above if you want to see who came second.

Nearest by drive time

Straight-line distance picks the wrong location when a river, a mountain range or a missing bridge is in the way. Checking drive time to every location would cost one route per customer per location, so check only a short list: the three nearest in a straight line.

1. In Customers!D2, list the three nearest names in separate cells (D, E and F) and copy it down:

   =ARRAYFORMULA(LET(d, IF(Stores!$B$2:$B$100="", 100000, 2*3958.8*ASIN(SQRT(SIN(RADIANS(Stores!$B$2:$B$100-$B2)/2)^2+COS(RADIANS($B2))*COS(RADIANS(Stores!$B$2:$B$100))*SIN(RADIANS(Stores!$C$2:$C$100-$C2)/2)^2))), TRANSPOSE(SORTN(Stores!$A$2:$A$100, 3, 0, d, TRUE))))

2. Make a Pairs tab with one row per customer and candidate. For 100 customers in rows 2 to 101:
   • A2 (customer): =ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1, 3), Customers!A2:A101)))
   • B2 (from): =ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1, 3), Customers!B2:B101&", "&Customers!C2:C101)))
   • C2 (candidate): =FLATTEN(Customers!D2:F101)
   • D2 (to): =ARRAYFORMULA(XLOOKUP(C2:C301, Stores!A2:A100, Stores!B2:B100&", "&Stores!C2:C100))
3. Select columns A to D, copy, and choose Edit → Paste special → Values only, so the pairs stop changing while you route them.
4. Get a driving time for each row with a routing tool. In MapRows, open the Distance tab, pick B as Origin and D as Destination, and choose Driving. Since both sides are coordinates, each route costs 1 lookup.
5. Back on Customers, pick the fastest candidate. If the travel minutes landed in column F of Pairs:

   =INDEX(SORTN(FILTER(Pairs!$C$2:$C$301, Pairs!$A$2:$A$301=A2), 1, 0, FILTER(Pairs!$F$2:$F$301, Pairs!$A$2:$A$301=A2), TRUE), 1)

Big sheets

Each formula row compares one customer with every location: 5,000 customers and 200 stores is a million distance calculations, which Google Sheets handles but recalculates slowly. To keep it quick:

• Set the store range to your real last row rather than whole columns.
• When the results are final, copy them and Paste special → Values only.
• Keep coordinates as numbers. Coordinates stored as text (often from a CSV) make every formula return an error until you convert them with Format → Number → Number or VALUE().

Troubleshooting

• #REF! with “Array result was not expanded”. The formula returns two (or more) columns, and the cell to its right isn’t empty. Clear the columns it needs.
• #VALUE! or every distance is an error. The coordinates are text, not numbers, which is common after a CSV import. Select the columns and choose Format → Number → Number, or wrap them in VALUE(). A quick test: =ISNUMBER(B2) should say TRUE.
• Everyone gets the same store. Usually a missing $: when the formula was copied down, the store range moved with it. Check that every Stores reference is written Stores!$B$2:$B$100, and the customer’s own cells ($B2, $C2) have no $ before the row number.
• Distances are thousands of miles too big. A longitude is missing its minus sign (everywhere in the Americas is west, so negative) or latitude and longitude are swapped. Plot a few rows on a map to check; see how to map addresses from Google Sheets.
• The nearest store shows as 0 miles. The customer and the store are the same point, or you’re comparing a list with itself. Use the “nearest other location” formula above.
• Formula parse error. In some locales, Sheets uses semicolons instead of commas between arguments, and a backslash instead of a comma inside {…}. Check File → Settings → Locale, or set it to United States while you paste.

Frequently asked questions

Related

• Distance between two coordinates in Google Sheets (haversine formula)
• How to geocode addresses in Google Sheets
• Calculate driving distance and travel time between addresses in Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. Every formula here works without it.

Google Sheets is a trademark of Google LLC. MapRows and Cellbrook are not affiliated with or endorsed by Google. ZIP Code is a trademark of the United States Postal Service.