How Do You Build a Job Title Normalization Sheet for Lead Routing?

Why does my lead routing break on job titles?

Lead routing breaks on job titles because people type their titles however they like. "VP Sales," "Vice President, Sales," and "Head of Revenue" can all mean the same buyer, but a routing rule that matches exact text treats them as three different people. A normalization sheet translates every raw title into one clean role and seniority.

This tutorial is for a specific situation: a small B2B team with inbound leads arriving through forms, a CRM like HubSpot, and routing rules that depend on who the person is. You do not have a RevOps team or a data warehouse. You do have Google Sheets and an afternoon.

By the end you will have a sheet that takes any job title and returns two clean fields, a role and a seniority level. Those two fields are what your routing rules should read, not the raw title the person typed.

What does a job title normalization sheet actually do?

It maps messy, free-text job titles to a short, fixed list of roles and seniority levels. First it checks an exact-match table of titles you have already seen. If there is no match, it falls back to keyword rules. Anything still unmatched is flagged so a person can review it and add it to the table.

That three-layer design is the whole idea. The exact-match table is precise but only knows titles you have seen. The keyword rules are broad and catch new variations. The review flag keeps the system honest, because every title it cannot handle becomes a new row instead of a silent routing mistake.

The output is deliberately small. I use a handful of roles, such as sales, marketing, operations, finance, and technical, and a few seniority levels, such as executive, director, manager, and individual contributor. Routing rules get much simpler when they choose from a short list instead of reading free text.

How do I set up the tabs in Google Sheets?

Create three tabs. The first, called Titles, holds the raw job titles you want to normalize. The second, called Map, holds known titles in one column with their role and seniority in the next two. The third, called Rules, holds keywords that point to a role or a seniority level when no exact match exists.

Start the Map tab by exporting job titles from your CRM. In HubSpot, the Job title contact property is the natural source. Remove duplicates, convert everything to lowercase, and sort by how often each title appears. The most common titles cover most of your leads, so map those first and leave the long tail for later.

Keep the Map tab in lowercase and trimmed of extra spaces, because that is how the formulas will compare titles. Consistency here saves you from strange mismatches later, where two titles look identical on screen but one has a trailing space the formula cannot see.

How do I write the exact-match lookup?

Use XLOOKUP to search the Map tab for the cleaned title and return its role, with a fallback value when nothing matches. Google's documentation describes XLOOKUP as returning values from a result range based on where a match was found in the lookup range, and it lets you set a value to return when no match is found.

In the Titles tab, put the raw title in column A. In column B, clean it with a formula like =LOWER(TRIM(A2)) so it matches the format of your Map tab. In column C, look up the role with =XLOOKUP(B2, Map!A:A, Map!B:B, "unmapped"). In column D, do the same for seniority using the third column of the Map tab.

The "unmapped" value matters. Without a missing value, XLOOKUP returns an error when it finds no match, and errors tend to break whatever reads the sheet next. A plain word is easier to filter, easier to count, and easier for an automation to handle.

How do I add keyword rules for titles I have not seen?

For any row still marked unmapped, test the cleaned title against keywords with REGEXMATCH, which Google describes as checking whether a piece of text matches a regular expression. A title containing "vp" or "vice president" maps to executive seniority. A title containing "marketing" or "demand" maps to the marketing role.

A practical way to write it is a nested IF in a new column. For seniority, something like =IF(REGEXMATCH(B2, "chief|vp|vice president|head of"), "executive", IF(REGEXMATCH(B2, "director"), "director", IF(REGEXMATCH(B2, "manager|lead"), "manager", "unmapped"))) covers the common cases. Write a similar column for role.

Order matters in keyword rules. "Head of" should be checked before "lead," and "assistant to the VP" should not become an executive. I keep the riskiest patterns in the Rules tab with a note explaining each one, so the next person who edits the sheet understands why the order is what it is.

How do I combine the exact match and the keyword rules?

Add a final column that uses the exact match when one exists and the keyword result when it does not. If both return unmapped, the row stays unmapped and goes to review. That final column is the only one your routing should read, which keeps the logic in one place you can inspect.

The formula is short: =IF(C2="unmapped", E2, C2) for role, where C holds the exact match and E holds the keyword result. Do the same for seniority. Then add a filter view that shows only rows where the final role or seniority is still unmapped.

That filter view is your weekly review queue. Each unmapped title gets a decision, and the decision goes into the Map tab as a new row. Over time the exact-match table grows, the keyword rules do less work, and fewer titles reach the queue at all.

How do I connect the sheet to my CRM routing?

Write the normalized role and seniority back to two custom properties on the contact, then build routing rules on those properties instead of the raw title. You can do the write-back with an automation tool such as Zapier, Make, or n8n, or with a periodic import if your volume is low.

For a low-volume team, a weekly export, normalize, and import cycle is often enough, and it has no moving parts to break. For live routing, an automation that sends each new title through the same logic is better. I would still keep the sheet as the single source of truth so the mapping lives in one place.

Once the properties exist, routing becomes readable. "Executive in marketing goes to the founder, director in operations goes to the senior rep" is a rule a person can check at a glance. If you are setting up the routing side in HubSpot, my round robin lead routing by territory tutorial pairs well with this.

How do I keep the sheet accurate over time?

Review the unmapped queue every week, add each new title to the Map tab, and spot-check a few mapped titles each month to catch rules that misfire. Keep a short change note in the sheet when you edit keyword rules. The sheet only stays useful if someone owns it and looks at it regularly.

The spot-check matters because keyword rules can quietly misclassify. A rule that maps "operations" to an operations role will also catch "revenue operations," which might belong with sales in your routing. Reading a sample of mapped titles is the only way to find those mistakes before they cost you a lead.

The same thinking applies to accounts, not just people. If you also sort companies into tiers for routing, my account tiering sheet tutorial uses a very similar structure, and the two sheets work well side by side.

What should you do next?

Export your contacts' job titles, build the three tabs, and map the most common titles first. Add the XLOOKUP and REGEXMATCH columns, set up the unmapped review filter, and create two clean properties in your CRM. Then rewrite one routing rule to read those properties and watch it for a week.

Start small. One routing rule built on clean data teaches you more than a complete rebuild. Once that rule works, move the rest over one at a time. If you route leads from a target account list as well, see how I approach routing inbound leads from a target account list.

If you want help building normalization and routing that holds up as your volume grows, reach out at pravinkumar.co. I build the systems behind go-to-market for B2B teams, and clean routing data is usually the first thing I fix.