Lookup
Fuzzy/Approximate Match
Find approximate text matches when exact matching fails. Useful for matching names with typos, product variations, or inconsistent data entry.
Excel Formula
=INDEX(B2:B100,MATCH(MIN(MMULT((CODE(MID(UPPER(A2),SEQUENCE(LEN(A2)),1))-CODE(MID(UPPER(B2),SEQUENCE(LEN(B2)),1)))^2,SEQUENCE(LEN(A2),1,1,0))),MMULT((CODE(MID(UPPER(A2),SEQUENCE(LEN(A2)),1))-CODE(MID(UPPER(B2),SEQUENCE(LEN(B2)),1)))^2,SEQUENCE(LEN(A2),1,1,0)),0))Google Sheets Version
=INDEX(B2:B100,MATCH(TRUE,REGEXMATCH(B2:B100,"(?i)"&A2),0))Step-by-Step Explanation
1
This finds the closest text match using character comparison
2
For practical use, a simpler approach often works better
3
Consider using wildcard MATCH or partial text matching
4
Google Sheets REGEXMATCH provides flexible pattern matching
5
For large datasets, consider helper columns with similarity scores
Example
| Search Term (A) | Match Found (B) |
|---|---|
| Microsft | Microsoft |
| Gogle | |
| Aple | Apple |
Result: The formula finds the closest matching text in your list
Common Variations
Wildcard match
=INDEX(B:B,MATCH("*"&A2&"*",B:B,0))Contains match with wildcards
Partial match
=INDEX(B:B,MATCH(A2&"*",B:B,0))Starts-with matching
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →