PHONETIC
The PHONETIC function searches for a lookup value within a lookup array using phonetic algorithms and returns an Excel Data Type containing the 1-based position of the first match, along with the matched text and phonetic codes for both the search term and the match.
Phonetic algorithms represent how words sound. This function supports Soundex, Metaphone, NYSIIS, and Match Rating Approach. A match is found if the phonetic codes for the lookup value and the array item are identical (or if the Match Rating comparison returns True).
Supported algorithms include Soundex, Metaphone, NYSIIS, and Match Rating Approach.
Excel Usage
=PHONETIC(lookup_value, lookup_array, phonetic_algo)lookup_value(list[list], required): 2D range of text values to search for.lookup_array(list[list], required): 2D range of text values to search within.phonetic_algo(str, optional, default: “soundex”): The phonetic algorithm to use.
Returns (list[list]): 2D list of Data Types containing the 1-based index, matched text, and phonetic codes.
Example 1: Soundex match
Inputs:
| lookup_value | lookup_array | phonetic_algo |
|---|---|---|
| Robert | Rupert | soundex |
| Smith |
Excel formula:
=PHONETIC({"Robert"}, {"Rupert";"Smith"}, "soundex")Expected output:
{"type":"Double","basicValue":1,"properties":{"Index":{"type":"Double","basicValue":1},"Match":{"type":"String","basicValue":"Rupert"},"Source Code":{"type":"String","basicValue":"R163"},"Match Code":{"type":"String","basicValue":"R163"}}}
Example 2: Metaphone match
Inputs:
| lookup_value | lookup_array | phonetic_algo |
|---|---|---|
| Dixon | Dickson | metaphone |
| Smith |
Excel formula:
=PHONETIC({"Dixon"}, {"Dickson";"Smith"}, "metaphone")Expected output:
{"type":"Double","basicValue":1,"properties":{"Index":{"type":"Double","basicValue":1},"Match":{"type":"String","basicValue":"Dickson"},"Source Code":{"type":"String","basicValue":"TKSN"},"Match Code":{"type":"String","basicValue":"TKSN"}}}
Example 3: Match Rating Approach
Inputs:
| lookup_value | lookup_array | phonetic_algo |
|---|---|---|
| Catherine | Katherine | match_rating |
| Smith |
Excel formula:
=PHONETIC({"Catherine"}, {"Katherine";"Smith"}, "match_rating")Expected output:
{"type":"Double","basicValue":1,"properties":{"Index":{"type":"Double","basicValue":1},"Match":{"type":"String","basicValue":"Katherine"},"Source Code":{"type":"String","basicValue":"CTHRN"},"Match Code":{"type":"String","basicValue":"KTHRN"}}}
Python Code
Show Code
import jellyfish
def phonetic(lookup_value, lookup_array, phonetic_algo='soundex'):
"""
Returns the 1-based index of the best phonetic match using the jellyfish library.
See: https://github.com/jamesturk/jellyfish
This example function is provided as-is without any representation of accuracy.
Args:
lookup_value (list[list]): 2D range of text values to search for.
lookup_array (list[list]): 2D range of text values to search within.
phonetic_algo (str, optional): The phonetic algorithm to use. Valid options: Soundex, Metaphone, NYSIIS, Match Rating. Default is 'soundex'.
Returns:
list[list]: 2D list of Data Types containing the 1-based index, matched text, and phonetic codes.
"""
try:
def to2d(x):
if x is None: return [[""]]
return [[x]] if not isinstance(x, list) else x
def get_phonetic_code(s, algo):
s = str(s)
if algo == "soundex":
return jellyfish.soundex(s)
elif algo == "metaphone":
return jellyfish.metaphone(s)
elif algo == "nysiis":
return jellyfish.nysiis(s)
elif algo == "match_rating":
return jellyfish.match_rating_codex(s)
return ""
def check_match(s1, s2, algo):
s1, s2 = str(s1), str(s2)
if algo == "soundex":
return jellyfish.soundex(s1) == jellyfish.soundex(s2)
elif algo == "metaphone":
return jellyfish.metaphone(s1) == jellyfish.metaphone(s2)
elif algo == "nysiis":
return jellyfish.nysiis(s1) == jellyfish.nysiis(s2)
elif algo == "match_rating":
return jellyfish.match_rating_comparison(s1, s2)
return False
needles = to2d(lookup_value)
haystack_flat = [item for sublist in lookup_array for item in sublist]
results = []
for row in needles:
res_row = []
for needle in row:
if needle is None or str(needle).strip() == "":
res_row.append("")
continue
best_idx = -1
matched_text = ""
for i, item in enumerate(haystack_flat):
if item is None:
continue
try:
if check_match(needle, item, phonetic_algo):
best_idx = i
matched_text = str(item)
break
except Exception:
continue
if best_idx != -1:
index = best_idx + 1
source_code = get_phonetic_code(needle, phonetic_algo)
match_code = get_phonetic_code(matched_text, phonetic_algo)
res_row.append({
"type": "Double",
"basicValue": float(index),
"properties": {
"Index": {"type": "Double", "basicValue": float(index)},
"Match": {"type": "String", "basicValue": matched_text},
"Source Code": {"type": "String", "basicValue": source_code},
"Match Code": {"type": "String", "basicValue": match_code}
}
})
else:
res_row.append("")
results.append(res_row)
return results
except Exception as e:
return f"Error: {str(e)}"