Two keys can look identical while differing in type or invisible characters. Investigate the exact strings before relaxing a lookup into an approximate or partial match.
Compare the source and requested key
Inspect the formula bar for both cells. Check leading or trailing spaces and whether one key is a number while the other is text. ISNUMBER can help identify a numeric representation.
Use a short helper comparison on known examples rather than changing the production key column. If leading zeros belong to the ID, the authoritative representation is usually text, not a number obtained by dropping them.
Normalize only agreed differences
TRIM removes ordinary leading, trailing, and repeated spaces. It does not remove every possible invisible character, including all nonbreaking-space cases. Identify imported characters before applying a targeted replacement.
Keep the raw key alongside a normalized helper key. Do not remove punctuation, internal spaces, or letter case unless the source system defines those differences as irrelevant. Otherwise distinct identifiers can collapse into the same value.
Check for normalization collisions
After creating helper keys, test previously successful lookups as well as failures. Count repeated normalized keys and inspect records that now match more than one source row. A newly successful match may be wrong if normalization merged unrelated accounts.
Retain an unmatched list for review and fix the recurring import where appropriate. Avoid using wildcard or approximate matching simply to improve the apparent match rate. Exact reconciliation requires an agreed identifier policy, not just visually similar output. Verify the returned record’s associated fields before replacing the working formula across a large dataset.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.