)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
HubSpotVLOOKUP fails when phone numbers have different formats between your Excel lead list and. Lead lists contain variations like (555) 123-4567, 555-123-4567, +1 555 123 4567, and 5551234567 that prevent exact matches.
Here’s how to use advanced Excel functions and phone normalization to match numbers regardless of formatting differences.
Replace VLOOKUP with flexible phone matching using Coefficient
Coefficienttransforms phone number matching by providing live HubSpot contact and company phone data that you can process with advanced Excel functions. You’ll work with current phone numbers from multiple fields like mobile, work, and company main numbers.
How to make it work
Step 1. Import comprehensive phone data from HubSpot.
Pull phone numbers from HubSpot contacts and companies using custom field selection. Include mobile phone, work phone, and company main numbers to ensure comprehensive phone matching against current CRM data rather than incomplete manual exports.
Step 2. Create phone normalization formulas.
Build formulas that strip formatting characters and create numeric-only versions: `=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”(“,””),”)”,””),”-“,””),” “,””),”+1″,””))`. This removes parentheses, dashes, spaces, and country codes to create standardized 10-digit numbers for comparison.
Step 3. Use XLOOKUP instead of VLOOKUP.
Replace VLOOKUP with XLOOKUP for more flexible phone matching: `=XLOOKUP(normalized_phone_column, hubspot_normalized_phones, hubspot_company_names, “No Match”, 0)`. XLOOKUP handles exact matches on your normalized phone numbers and returns company information from any column position.
Step 4. Apply INDEX/MATCH for bidirectional lookups.
Use INDEX/MATCH combinations when you need to search phone numbers in either direction: `=INDEX(company_names, MATCH(normalized_phone, phone_lookup_range, 0))`. This works regardless of column order and provides more flexibility than VLOOKUP’s left-to-right limitation.
Step 5. Set up dynamic phone matching workflows.
Use Coefficient’s dynamic filtering to point filter values to normalized phone numbers from your Excel lead list. This automatically imports only HubSpot records with similar phone patterns, creating focused datasets for more efficient matching.
Step 6. Combine phone matching with name validation.
Use Coefficient’s association handling to combine phone matching with name or company matching. This reduces false positives caused by phone number recycling and shared business lines: `=IF(AND(phone_match, name_similarity>0.8), “High Confidence Match”, “Review Required”)`.
Match phone numbers regardless of formatting differences
Build betterAdvanced Excel functions combined with phone normalization provide far more robust matching than basic VLOOKUP. You’ll catch matches that formatting variations would otherwise prevent.phone matching workflows today.
