How to find the nearest location by coordinates in Excel
Formula (Excel and Google Sheets)
=LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7))Work out every distance and look up the smallest: =LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7)).
- Result on the example
- Riverside in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
For each warehouse, SQRT((B2:B7-G1)^2+(C2:C7-I1)^2) is the straight-line distance to the customer, by Pythagoras. LET names that list dist, MIN finds the shortest, 9.8 miles, and XLOOKUP returns the warehouse it belongs to, Riverside, just ahead of Hillcrest at 9.9.
=LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7))Result"Riverside"
dist{6 rows × 1 col} 31.82766093, 9.848857802, 9.899494937, 24.18677324, …A name defined in LET, or a named range.
B2:B76 rows × 1 col: 12, 38, 27, 55, …A range: 6 cells in one column.
G134The value in G1.
B2:B7-G1{6 rows × 1 col} -22, 4, -7, 21, …The - operator subtracts.
B2:B7-G1)^2{6 rows × 1 col} 484, 16, 49, 441, …The ^ operator raises to a power.
C2:C76 rows × 1 col: 41, 9, 25, 30, …A range: 6 cells in one column.
I118The value in I1.
C2:C7-I1{6 rows × 1 col} 23, -9, 7, 12, …The - operator subtracts.
C2:C7-I1)^2{6 rows × 1 col} 529, 81, 49, 144, …The ^ operator raises to a power.
B2:B7-G1)^2+(C2:C7-I1)^2{6 rows × 1 col} 1013, 97, 98, 585, …The + operator adds.
SQRT((B2:B7-G1)^2+(C2:C7-I1)^2){6 rows × 1 col} 31.82766093, 9.848857802, 9.899494937, 24.18677324, …SQRT: Square root.- number
B2:B7-G1)^2+(C2:C7-I1)^2{6 rows × 1 col} 1013, 97, 98, 585, …
- number
MIN(dist)9.848857802MIN: Smallest number.- number1
dist{6 rows × 1 col} 31.82766093, 9.848857802, 9.899494937, 24.18677324, …
- number1
A2:A76 rows × 1 col: "Northgate", "Riverside", "Hillcrest", "Bayview", …A range: 6 cells in one column.
XLOOKUP(MIN(dist),dist,A2:A7)"Riverside"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
MIN(dist)9.848857802 - lookup_array
dist{6 rows × 1 col} 31.82766093, 9.848857802, 9.899494937, 24.18677324, … - return_array
A2:A76 rows × 1 col: "Northgate", "Riverside", "Hillcrest", "Bayview", …
- lookup_value
LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7))"Riverside"LET: Names values inside a formula.- name1
dist{6 rows × 1 col} 31.82766093, 9.848857802, 9.899494937, 24.18677324, … - value1
SQRT((B2:B7-G1)^2+(C2:C7-I1)^2){6 rows × 1 col} 31.82766093, 9.848857802, 9.899494937, 24.18677324, … - name2, value2
XLOOKUP(MIN(dist),dist,A2:A7)"Riverside"
- name1
In Google Sheets
The same formula works in Google Sheets: =LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7)). We ran it in Google Sheets mode on the same data and it returns Riverside.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A7,MATCH(MIN((B2:B7-G1)^2+(C2:C7-I1)^2),(B2:B7-G1)^2+(C2:C7-I1)^2,0)) | Riverside | Every version (Ctrl+Shift+Enter in Excel 2019 and earlier). Squared distances rank the same way, so SQRT can be skipped. |
Common mistakes
- Latitude and longitude aren't miles: a degree of longitude shrinks toward the poles. Multiply the longitude difference by
COS(RADIANS(latitude))before squaring, or use the haversine formula for long distances. - Straight-line distance ignores roads. For delivery times, use a routing service.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the nearest location by coordinates
G2, return the warehouse closest in a straight line to the customer at G1, I1.Questions
How do I return the distance too?
Return MIN(dist) from the same LET in the next cell.
How do I list the three nearest?
Sort by distance: =TAKE(SORTBY(A2:A7,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2)),3).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.