How to Find Closest Location from List in Google Sheets

To find the closest location from a list in Google Sheets, install the free InstaMaps add-on and use the formula =CLOSEST_TO("123 Main St", A2:A50). This function returns the nearest address from your specified range, making it easy to route service crews or assign leads based on actual driving distance.

This process is built for sales operations teams, regional managers, and local service businesses trying to assign inbound inquiries to the nearest rep. Instead of manually cross-referencing postcodes, paying for expensive routing software, or exporting data to third-party mapping sites, you will have a fully automated spreadsheet. It pairs customers with your closest branch, prospect, or field agent entirely within Google Sheets.

What you need before you start

Before you begin, you need a Google Sheet containing your list of target locations-these could be inbound leads, existing customers, or your team's branch addresses. You also need the free InstaMaps add-on installed in your Google Workspace account.

Once installed, you access the tool from the Extensions menu. A dedicated sidebar (Extensions > InstaMaps > Formulas) allows you to insert these location formulas without typing the syntax manually. If your source data is messy, you can standardise it first using the =CLEAN_ADDRESS(A2) formula to ensure accurate distance calculations.

Step 1: Find the closest location from your list

Ensure your target addresses are in a single column. If you have a specific customer address and want to find the nearest rep from your team list, use the CLOSEST_TO function. This function evaluates the straight-line distance between your target point and every address in your list, returning the single closest match.

If your customer is in cell B2 and your reps' addresses are in column A2:A20, type: =CLOSEST_TO(B2, A2:A20). The cell will output the exact rep address that is physically closest to that customer. You can drag this formula down an entire column to calculate the nearest rep for hundreds of new leads automatically. This completely eliminates the need to manually sort postcodes or guess which rep covers which street.

Step 2: Find all locations within a specific radius

Sometimes you do not just want the single closest location; you want to see all viable options within a specific service area. For example, if you are managing a 200-home farm and want to identify all prospects within a short drive of a key lighthouse account, use the WITHIN_RADIUS function. This filters your list based on a distance threshold you define.

To find all prospects within 25 miles of a lighthouse account in cell C2, type: =WITHIN_RADIUS(C2, A2:A100, 25). The formula outputs a clean, comma-separated list of every address from your selected range that falls inside that 25-mile radius. This is particularly useful for scheduling batch visits or defining temporary sales territories around a new product launch.

Step 3: Map your closest locations

Once you have matched your leads to the nearest reps, you need to visualise the data to confirm your assignments make geographic sense. InstaMaps generates a hosted map URL directly inside your spreadsheet, which updates automatically whenever you add new leads or change rep assignments.

To create your map, type: =INSTAMAP(A2:B50). The cell output displays a clickable hyperlink to a fully interactive web map showing your data points. You can also access pre-built layouts at get-instamaps.com/templates to speed up your workflow. To give your field reps exact turn-by-turn navigation, use the =ROUTE_LINK() function. If you type =ROUTE_LINK(B2, A2:A8), it generates a Google Maps navigation link. Note that this specific routing formula is capped at a maximum of 11 stops per link by Google's official URL scheme limits.

Worked example: Assigning leads to a regional sales team

Consider a sales operations manager handling a recent inbound batch of 47 leads across the Midlands. The team consists of 5 field sales reps. Manually identifying which rep covers which postcode is tedious, prone to error, and often leads to unbalanced territories where reps drive past each other.

Instead of manual sorting, the manager lists the 47 leads in Column A and the 5 reps' home office addresses in Column G. By running =CLOSEST_TO(A2, $G$2:$G$6) down Column B, the manager instantly pairs all 47 leads with the geographically closest rep. To expand the options, the manager could use =WITHIN_RADIUS(A2, $G$2:$G$6, 15) to identify which reps are within a 15-mile radius of specific high-value leads. Finally, they use =INSTAMAP(A2:B47) to generate a link to share with the regional director, visually proving that the territories are balanced and travel time is minimised.

Limits and honest alternatives

InstaMaps is free to use, but it operates on a daily lookup quota to manage server loads and prevent abuse. The standard free tier provides 100 lookups per day. You can increase this to 1,000 lookups per day by registering with a free email directly within the add-on interface. This limit applies to functions that require external geocoding or distance matrix calculations.

If you are processing massive datasets with tens of thousands of rows daily, or if you need complex route optimisation with vehicle capacity constraints and traffic-aware timings, a dedicated paid routing tool may be better suited for your needs. InstaMaps is designed for ad-hoc spreadsheet analysis, territory alignment, and straightforward proximity matching. If you simply need to measure the standard driving distance between two specific points rather than scanning a whole list, use =DISTANCE(A2, B2).

Common Questions