How to assign leads to nearest rep in Google Sheets

To assign leads to the nearest rep in Google Sheets, use the InstaMaps add-on. Place your lead coordinates in column A and your rep coordinates in column B. Apply the formula =CLOSEST_TO(A2, B$2:B$15) in column C to output the exact rep coordinate assigned to that specific lead.

This setup is for field sales managers routing high daily lead volumes. By combining =CLOSEST_TO() with a designated output column, your sheet becomes an automated dispatcher, matching every new lead to the correct rep without manual post-code checks or custom scripts.

Setting up your lead and rep data

Separate your data into two distinct tabs: 'Leads' and 'Reps'. Keeping them on separate tabs prevents reference errors when you add new inbound rows to the lead sheet. Structure both tabs identically to ensure your formulas map across the correct columns.

In the 'Leads' tab, structure your columns as follows: Column A for Lead ID, Column B for Address, and Column C for Coordinates. In the 'Reps' tab, use Column A for Rep Name, Column B for Address, and Column C for Coordinates. If you have a 120-row lead list, your active data range will be A2:C121.

To turn pasted addresses into latitude and longitude coordinates, use the =GEOCODE() function. In cell C2 of the 'Leads' tab, input =GEOCODE(B2). Press Enter, then double-click the fill handle on the bottom-right corner of cell C2 to drag the formula down to C121. Repeat this exact process on the 'Reps' tab in cell C2, dragging it down to cover your total number of reps.

InstaMaps processes 100 lookups per day on the standard free tier. Generating 120 lead coordinates and 5 rep coordinates requires 125 lookups. You can increase this limit to 1,000 lookups per day with a free email unlock directly from the add-on interface.

Once the coordinates generate, lock the data. Highlight Column C, copy the values, and use Edit > Paste special > Paste values only. This overwrites the formulas with hard text (e.g., 51.5074, -0.1278), preventing the sheet from recalculating and consuming your daily lookup quota every time you open the document.

Assigning leads using =CLOSEST_TO()

Once your coordinates are hardcoded into Column C on both tabs, build the auto-assignment column. Go to the 'Leads' tab. Label cell D1 as 'Assigned Rep'. In cell D2, write the following formula: =CLOSEST_TO(C2, Reps!C$2:C$6, Reps!A$2:A$6).

The =CLOSEST_TO() function requires three specific arguments to process the assignment. The syntax dictates the target point, the search range, and the return range. Understanding these cell references is necessary to avoid incorrect routing.

1. Target Point (C2): This is the cell containing the latitude and longitude for your specific lead in row 2.

2. Search Range (Reps!C$2:C$6): This range points to the coordinates of your reps. The dollar signs ($) lock the row numbers so the range stays fixed on your team when you drag the formula down. If you add a sixth rep later, change this to C$2:C$7.

3. Return Range (Reps!A$2:A$6): This range must mirror the exact size of the search range. When the formula finds the closest coordinate in the search range, it returns the corresponding value from this return range-in this instance, the rep's name from Column A.

Filtering unassigned leads with =TERRITORY()

Proximity does not guarantee coverage. The closest rep to a lead in Bristol might technically be a rep based in Cardiff, but company policy might dictate that representatives do not cross the Severn Bridge. To prevent cross-border assignments, use the =TERRITORY() function to verify that the lead falls within an approved geographic zone.

On the 'Leads' tab, label cell E1 as 'Territory Check'. In cell E2, input the following formula: =TERRITORY(C2, "England"). This checks the lead's coordinates in C2 against a predefined geographic boundary. If the coordinates fall within England, the cell outputs TRUE. If they fall outside, it outputs FALSE.

Combine this with your assignment logic to handle out-of-area leads. Label cell F1 as 'Final Status'. In F2, write: =IF(E2=TRUE, D2, "Unassigned - Out of Area"). This formula evaluates the territory check in E2. If it reads TRUE, it pulls the assigned rep's name from D2. If it reads FALSE, it overwrites the assignment and flags the lead as out of area.

Filter Column F for "Unassigned - Out of Area". These leads require manual review or routing to a centralised dispatch team rather than your field reps. This two-step process ensures reps only receive leads they are legally and operationally permitted to service.

Worked example: 5 field reps and 120 inbound leads

Consider a facilities management company that receives 120 inbound service requests overnight for emergency HVAC repairs across the South East. They have 5 technicians on duty. The goal is to assign the nearest technician to each of the 120 locations.

1. Step 1: Prepare the Reps tab. Enter the names of the 5 technicians in A2:A6 (James, Sarah, David, Priya, Mark). Enter their home postcodes in B2:B6. In C2, input =GEOCODE(B2) and drag down to C6. Highlight C2:C6, copy, and paste as values.

2. Step 2: Prepare the Leads tab. Paste the 120 lead IDs into A2:A121. Paste the customer addresses into B2:B121. In C2, type =GEOCODE(B2) and drag down to C121. Copy and paste as values.

3. Step 3: Auto-assign the jobs. In D1, type 'Assigned Tech'. In D2, apply the closest match formula: =CLOSEST_TO(C2, Reps!C$2:C$6, Reps!A$2:A$6). Double-click the fill handle to populate all 120 rows down to D121.

4. Step 4: Validate territories. In E2, type =TERRITORY(C2, "Greater London") to verify which jobs are inside the M25. Drag down to E121.

Generating a live map with =INSTAMAP()

To visualise the lead distribution, place the =INSTAMAP() function in a dedicated cell outside your main data range, such as H1. If your newly assigned rep addresses are in column D (D2:D150) and the corresponding lead coordinates are in column C (C2:C150), construct the formula as =INSTAMAP(C2:C150, D2:D150). Pressing Enter does not output a chart inside the cell; instead, it generates a hosted URL link. Clicking this link opens a separate browser tab containing an interactive map of your assigned territories.

You can also pass text labels into the map pins. If you want to display the lead's postcode (column B) alongside the rep's assigned territory (column F), format the formula as =INSTAMAP(C2:C500, B2:B500, F2:F500). Alternatively, instead of typing the ranges manually, access the InstaMaps sidebar (Extensions > InstaMaps > Enable formulas) and use its Build-the-workflow button to insert the exact syntax based on your highlighted columns.

This hosted map is strictly dynamic. If a sales-ops manager pastes 50 new leads into row 501, the =CLOSEST_TO() and =TERRITORY() formulas auto-populate the rep assignments, and the =INSTAMAP() link automatically updates to plot these new coordinates. The map refreshes when the sheet changes, allowing you to share the URL directly with regional directors who lack edit access to the underlying Google Sheet.

Limits and honest alternatives

The primary constraint of this routing method is the daily lookup quota. The InstaMaps free tier permits 100 lookups per day. Registering an email address with the add-on increases this limit to 1,000 lookups per day. If you import a CSV of 1,500 leads at once, the =CLOSEST_TO() formulas will process the first 1,000 rows and return a daily quota error for rows 1001 to 1500 until the clock resets.

A second constraint involves geometric routing accuracy. The =CLOSEST_TO() function calculates straight-line distance between the lead's coordinates and the rep's coordinates. It does not account for traffic, road closures, or physical geography like rivers or motorways. A lead that is 2 miles away as the crow flies might require a 20-minute drive across a bridge, making a different rep the better choice. When straight-line distance is insufficient, you can calculate actual driving durations using the =TRAVEL_TIME() function, but this consumes more daily lookups per query.

Finally, this workflow operates entirely within a spreadsheet. It does not automatically push data back into a CRM. If your company relies on Salesforce or HubSpot for automated cadences, pipeline tracking, or commission calculations, this Sheet serves as a staging environment. Once the =CLOSEST_TO() column assigns the rep, an admin must export the sheet or use a third-party connector to update the CRM record. If your team requires real-time API routing or dynamic polygon drawing based on census data, switch to dedicated territory mapping software, which costs thousands of pounds annually but provides deeper database integration.

Who this workflow is for

This spreadsheet setup is built for sales-ops managers, dispatchers, and local service coordinators handling regional fleets of 5 to 50 field reps. If you currently manage lead distribution by manually copying addresses from an inbox, pasting them into Google Maps, and texting the rep, this workflow automates that exact bottleneck.

It is ideal for teams processing 100 to 500 inbound leads per week-such as roofing contractors, franchise territories, or B2B field sales teams-where knowing the closest rep prevents a 45-minute drive across a metropolitan area. It is not for enterprise sales directors managing national queues of 50,000+ leads with complex rules based on account value, industry vertical, or CRM stage. If your lead assignment requires factoring in a rep's current pipeline revenue, historical win rates, or custom SLA timers, a Google Sheet cannot compute those variables. This setup works strictly for geographic proximity.

Common Questions