SORT_BY_DISTANCE: Sort Addresses by Distance in Google Sheets

Use the =SORT_BY_DISTANCE() custom formula from the InstaMaps add-on to arrange a list of locations from a specific point. If you enter =SORT_BY_DISTANCE(A2:D50, "Buckingham Palace, London"), the function outputs the entire data range sorted from closest to furthest.

Built for delivery drivers coordinating 47 daily stops and regional sales managers routing a 5-person crew, this function replaces manual mapping. You paste your raw location list and receive a structured sheet prioritised by physical proximity to your depot.

Syntax and Arguments

The `=SORT_BY_DISTANCE()` function calculates the straight-line distance from a designated origin point to a list of locations, then returns an entire dataset sorted from nearest to furthest. It outputs a dynamic array that automatically updates when the underlying sheet changes.

**Syntax**
`=SORT_BY_DISTANCE(origin, location_range, data_range, [is_ascending])`

1. **`origin`** (required): The single point from which distances are measured. This accepts a physical address (e.g., `"10 Downing St, London"`), a postcode (e.g., `"SW1A 2AA"`), a coordinate string (`"51.5034, -0.1276"`), or a single cell reference containing one of these values.

2. **`location_range`** (required): A single column of cells containing the destination addresses or coordinates. The dimensions of `location_range` must exactly match the height (number of rows) of the `data_range` to avoid a `#VALUE!` error.

3. **`data_range`** (required): The entire range of rows you want to return in the output. Because the function returns whole rows nearest-first, this range includes all adjacent columns (e.g., names, phone numbers, addresses).

4. **`[is_ascending]`** (optional): A boolean value determining the sort direction. Use `TRUE` or `1` (default) to sort from closest to furthest. Use `FALSE` or `0` to sort from furthest to closest.

5. **Technical Constraints**: The formula relies on the InstaMaps geocoder. If `location_range` contains raw text addresses rather than pre-geocoded coordinates, each cell counts as one lookup against the daily quota. The free tier allows 100 lookups/day (1,000/day with a free email unlock). For datasets exceeding 1,000 rows, pre-geocode your addresses using the [geocoding formula](/google-sheets-geocode-formula) and point the `location_range` to the coordinate column to conserve daily limits.

6. **Array Spilling**: The function returns an array. If you place the formula in `G2` and the output requires 15 rows, cells `G3:G16` must be empty. Any existing data in the spill path results in a `#REF!` error.

Worked Example: Sorting 47 Service Stops

A facilities manager oversees 47 HVAC maintenance sites scattered across Greater London. The team dispatches technicians from a central depot in Croydon. The goal is to sort the entire list of sites by distance from the depot, ensuring the closest jobs appear at the top of the daily roster.

The raw data occupies `A1:D48`.

* Column A: `Site_ID` (e.g., `HVAC-001`)
* Column B: `Site_Name`
* Column C: `Address`
* Column D: `Last_Inspection`

The depot address, `"Unit 4, Purley Way, Croydon"`, is stored in cell `H1`.

1. **1. Designate the output area:** Click cell `F2` to establish the start of the sorted output array. Ensure the adjacent columns `F:I` are clear of any existing text or formatting down to row 48 to prevent a `#REF!` spill error.

2. **2. Enter the formula:** Type `=SORT_BY_DISTANCE(H1, C2:C48, A2:D48)`. The `location_range` (`C2:C48`) precisely matches the 47 rows of the `data_range` (`A2:D48`). The `[is_ascending]` argument is omitted, defaulting to nearest-first.

3. **3. Execute the sort:** Press Enter. The function geocodes the depot in `H1` and the 47 addresses in `C2:C48`. It calculates the straight-line distance from the depot to each address and spills the sorted rows into `F2:I48`.

Composes With

The utility of `=SORT_BY_DISTANCE()` increases when chained with other InstaMaps functions. Because it outputs a standard dynamic array, you can feed its results directly into mapping or filtering formulas. You can write these chains manually, or click the **Build-the-workflow** button in the InstaMaps sidebar to generate them automatically.

1. **1. Filter by radius, then sort by distance:** If you only want to see maintenance sites within a 25-mile driving radius of your depot, apply `=WITHIN_RADIUS()` to filter the dataset before passing it into `SORT_BY_DISTANCE()`. Because `SORT_BY_DISTANCE` requires two ranges of matching height, wrap `FILTER()` around both the address column and the full data range:
   `=SORT_BY_DISTANCE(H1, FILTER(C2:C100, WITHIN_RADIUS(H1, C2:C100, 25)), FILTER(A2:D100, WITHIN_RADIUS(H1, C2:C100, 25)))`
   This evaluates the 99 rows in `A2:D100`. The `WITHIN_RADIUS` function flags only the sites within 25 miles of `H1`. `FILTER` discards the rest. Finally, `SORT_BY_DISTANCE` takes the reduced list and sorts it from nearest to furthest.

2. **2. Sort addresses, then generate a live map:** Once you have a sorted array of addresses, pass that range directly into `=INSTAMAP()` to generate a live, hosted shareable map URL. The map updates automatically when you add or remove addresses from the original dataset. Assuming your sorted array spills into `F2:I48`, enter the following in cell `K1`:
   `=INSTAMAP(F2:I48)`
   Because `INSTAMAP()` plots points in the order they appear in the range, the map pins will reflect the sorted proximity. If a field technician needs turn-by-turn navigation for the first 10 stops, copy the addresses from the first ten rows of the spill range and pass them into `=ROUTE_LINK()`. This uses Google Maps' official URL scheme, which supports a strict maximum of 11 stops.

Common Errors

When =SORT_BY_DISTANCE() fails, the cell output identifies the specific issue. The most frequent error is #NAME? in the cell (for example, B2 returning #NAME?). This indicates that Google Sheets does not recognise the =SORT_BY_DISTANCE() syntax. The InstaMaps add-on is either not installed in this specific workbook, or you have not granted the script the necessary permissions to run. To resolve this, install the add-on from the Google Workspace Marketplace, then navigate to Extensions > InstaMaps > Enable formulas. For a deeper technical breakdown of why custom functions throw this error and how to refresh authentication tokens, read our guide on the [google-sheets-geocode-formula] and resolving #NAME? errors.

You will encounter #VALUE! if the origin or target ranges contain text strings that are not physical locations. If your origin cell C2 contains the text 'Headquarters' instead of a verifiable address or specific coordinate pair like '51.5074, -0.1278', the underlying geocoder fails. Pre-process raw CRM text using =CLEAN_ADDRESS() on the dataset first to ensure the targets are structured correctly.

A #REF! error occurs when the formula attempts to output a sorted array but encounters existing data in the destination cells. If you instruct the formula to sort A2:E500 (a 5-column, 499-row output array) but cell F10 already contains text, the sort halts and returns #REF!. Ensure the area below and to the right of your formula cell is entirely blank.

When processing large datasets, you might see a cell output stating 'Daily quota exceeded'. Each target row in the target_range requires one distance calculation against the origin. Sorting a 250-row sheet consumes 250 daily lookups. When you hit the 100-lookup free tier ceiling, the remaining rows return a quota error message instead of a distance. Logging in with an email unlocks the 1,000/day tier, which accommodates most standard operational spreadsheets. For datasets exceeding 1,000 rows, the function will time out, requiring you to split your data into separate tabs.

Limits and Honest Alternatives

=SORT_BY_DISTANCE() calculates straight-line (as the crow flies) distances from a single origin point to a list of targets. It outputs the entire specified dataset, re-ordered from nearest to furthest based on that linear distance.

Use this function when you need a straightforward, linear ranking of locations based on proximity. It is ideal for basic dispatch tasks, such as finding the 50 closest customers to a new store location out of a master list of 2,000 addresses. Combine it with =INSTAMAP(A2:E50) to instantly visualise the sorted data on a live hosted shareable map URL.

Do not use this function if you are routing a multi-stop driving vehicle. Knowing that Stop B is closer to the depot than Stop C does not account for road layouts, motorway access, or one-way systems. Sorting 30 addresses by distance from your warehouse will not provide an efficient driving sequence.

When to switch to =VISIT_ORDER(): If you need to route a delivery van between 12 stops, use =VISIT_ORDER(). Instead of sorting purely by proximity to the start point, =VISIT_ORDER(A2:A12) calculates the most efficient driving sequence, factoring in road networks to minimise overall travel time.

When to switch to =ROUTE_LINK(): If you have a fixed sequence of stops and simply need to push them to a navigation app, use =ROUTE_LINK(). It uses Google Maps' official URL scheme to generate a direct turn-by-turn link, with a hard limit of 11 stops per link.

Capacity Limits: The InstaMaps add-on is free. The free tier allows 100 lookups/day (a lookup is defined as one origin-to-target calculation). This increases to 1,000 lookups/day with a free email unlock.

Who This is For

This function is built for operational users who need spatial awareness inside a spreadsheet, without migrating their data to a dedicated GIS platform.

1. Local delivery dispatchers: Managers assigning 60 to 100 daily orders to a fleet of 4 vehicles. Instead of manually scanning a list of 500 postcodes, dispatchers use =SORT_BY_DISTANCE(A2:E500, H2) against the warehouse coordinates to segment their data. They can quickly isolate the nearest 20 stops to assign to Zone 1.

2. Field sales representatives: Sales reps managing a large county territory containing 300 potential clients. Before a two-day trip, a rep sorts their prospect list by distance from their hotel. This generates a targeted list of the 15 closest leads, ensuring they do not waste fuel driving across the region for a single meeting.

3. Real estate analysts: Agents filtering a 400-property export from an MLS database. By sorting properties by distance from a specific postcode or city centre, analysts rapidly generate relevant shortlists for buyers moving to a specific neighbourhood.

4. Event coordinators: Planners evaluating vendor proximity. Sorting a list of 50 equipment rental companies by distance from the venue allows coordinators to identify the closest local vendors, minimising delivery call-out fees.

Common Questions