Fix #NAME? in Formulas Written from Python (XLOOKUP, TEXTJOIN, IFS)

A script writes a formula that works perfectly when typed into Excel:

ws["D2"] = '=XLOOKUP(A2, Prices!A:A, Prices!B:B)'

and the cell shows #NAME? when the workbook is opened. The tell-tale sign is that clicking into the cell and pressing Enter fixes it. Nothing is wrong with the formula; what is wrong is how it is stored. Functions added to Excel after the original .xlsx specification are saved in the file with an _xlfn. prefix, and openpyxl writes exactly the text you give it. The spreadsheet reads a function called plain XLOOKUP, does not recognise it as the new function, and reports an unknown name.

The same error has a handful of other causes that also come up only when formulas are generated rather than typed. This page, part of Troubleshooting Common Python Excel Errors, covers all of them and ends with a linter that checks every formula before the workbook is saved. The results below were checked by recalculating the files in LibreOffice, which applies the same rule.

Prerequisites

pip install openpyxl xlsxwriter

import re
from openpyxl import Workbook, load_workbook
from openpyxl.utils import FORMULAE
from openpyxl.workbook.defined_name import DefinedName

Step 1 — Add the _xlfn. prefix to newer functions

When writing with openpyxl, put the prefix in the formula text yourself. Users never see it — Excel hides it in the formula bar — but the file needs it:

wb = Workbook()
ws = wb.active
ws.append(["sku", "price"])
ws.append(["A-1", 4.5])
ws.append(["B-2", 7.25])

ws["D1"] = '=_xlfn.XLOOKUP("B-2",A2:A3,B2:B3)'
ws["D2"] = '=_xlfn.TEXTJOIN(", ",TRUE,A2:A3)'
ws["D3"] = '=_xlfn.IFS(B2>5,"high",TRUE,"low")'
ws["D4"] = '=_xlfn.STDEV.S(B2:B3)'
wb.save("prefixed.xlsx")
print([ws.cell(row=r, column=4).value for r in range(1, 5)])

The functions that need it are the ones Excel gained from 2010 onwards: XLOOKUP, XMATCH, TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS, MINIFS, IFNA, STDEV.S, STDEV.P, VAR.S, PERCENTILE.INC, RANK.EQ, AGGREGATE, DAYS, LET, LAMBDA, the dynamic array functions such as UNIQUE, SEQUENCE and SORTBY, and many statistics functions with dotted names. Two of the dynamic array functions have a longer prefix: FILTER and SORT are stored as _xlfn._xlws.FILTER and _xlfn._xlws.SORT.

openpyxl ships the list of functions that do not need a prefix, openpyxl.utils.FORMULAE. Checking a name against it is the quickest test:

for name in ["SUM", "VLOOKUP", "SUMIFS", "IFERROR", "XLOOKUP", "TEXTJOIN", "IFS", "STDEV.S"]:
    print(f"{name:9} {'plain' if name in FORMULAE else 'needs _xlfn. (or is misspelled)'}")

Step 2 — Or let xlsxwriter add prefixes for you

When the workbook is new, xlsxwriter can apply the prefixes itself. Pass use_future_functions when creating the workbook and write formulas the way you would type them:

import xlsxwriter
import zipfile

book = xlsxwriter.Workbook("auto_prefixed.xlsx", {"use_future_functions": True})
sheet = book.add_worksheet()
sheet.write_column("A1", ["A-1", "B-2"])
sheet.write_column("B1", [4.5, 7.25])
sheet.write_formula("D1", '=XLOOKUP("B-2",A1:A2,B1:B2)')
sheet.write_formula("D2", "=STDEV.S(B1:B2)")
sheet.write_formula("D3", "=FILTER(A1:A2,B1:B2>5)")
book.close()

with zipfile.ZipFile("auto_prefixed.xlsx") as z:
    xml = z.read("xl/worksheets/sheet1.xml").decode()
print(re.findall(r"<f[^>]*>(.*?)</f>", xml))

The stored formulas carry _xlfn. and, for FILTER, _xlfn._xlws.. With pandas, pass the same option through engine_kwargs={"options": {"use_future_functions": True}} on an ExcelWriter using the xlsxwriter engine. Dynamic array functions have one more storage detail — they are written as array formulas so the results can spill — which Write Dynamic Array Formulas like FILTER and UNIQUE from Python covers.

Step 3 — Check the other causes of #NAME?

If the function is an old one, or already prefixed, the error has one of these causes instead. Each was confirmed by writing it with openpyxl and recalculating:

Undefined names. A formula that refers to a named range the workbook does not have — because the name was never created, or was created with a typo, or belongs to a different workbook — fails the same way. Create the defined name before relying on it:

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws.append(["amount"])
ws.append([120])
ws.append([80])
wb.defined_names["Amounts"] = DefinedName("Amounts", attr_text="Data!$A$2:$A$3")
ws["C1"] = "=SUM(Amounts)"
wb.save("named.xlsx")
print(list(wb.defined_names.keys()))

Text without quotes. Inside a formula, a bare word is read as a name. =IF(A2=Yes,...) looks for a defined name called Yes. Text constants need double quotes, which is easy to get wrong when a formula is assembled with an f-string. Build formula text with a helper that quotes values, and double any quotes inside them:

def xl_text(value):
    return '"' + str(value).replace('"', '""') + '"'

status = 'Say "yes"'
print(f"=IF(A2={xl_text(status)},1,0)")
# =IF(A2="Say ""yes""",1,0)

Localised function names and separators. The file format always uses English function names and commas between arguments. A formula copied from a German or Spanish Excel — =SUMME(A1;A2), =SUMA(A1;A2) — must be translated before it is written; Excel converts it back to the reader's language on screen. Semicolons as separators produce errors as well, though not always #NAME?.

Sheet names with spaces. =Sales Data!A1 is not #NAME? but it is still broken: sheet names containing spaces or punctuation must be quoted, ='Sales Data'!A1. Write Formulas That Reference Other Sheets with openpyxl has a helper that quotes them correctly.

Step 4 — Lint every formula before saving

Collect the rules into one function that inspects every formula in a workbook and reports what would break. It lists function names that are neither in FORMULAE nor prefixed, names that look like defined names but are not defined, and can apply the prefix for functions you tell it about:

FUNC = re.compile(r"(?<![\w.])([A-Z][A-Z0-9]*(?:\.[A-Z0-9]+)*)\(")
PREFIX = {"XLOOKUP": "_xlfn.", "XMATCH": "_xlfn.", "TEXTJOIN": "_xlfn.", "CONCAT": "_xlfn.",
          "IFS": "_xlfn.", "SWITCH": "_xlfn.", "MAXIFS": "_xlfn.", "MINIFS": "_xlfn.",
          "IFNA": "_xlfn.", "STDEV.S": "_xlfn.", "STDEV.P": "_xlfn.", "LET": "_xlfn.",
          "UNIQUE": "_xlfn.", "FILTER": "_xlfn._xlws.", "SORT": "_xlfn._xlws."}

def lint_formulas(wb, fix=False):
    problems = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if not (isinstance(cell.value, str) and cell.value.startswith("=")):
                    continue
                text = re.sub(r'"[^"]*"', '""', cell.value)      # ignore string literals
                for name in set(FUNC.findall(text)):
                    if name in FORMULAE:
                        continue
                    if fix and name in PREFIX:
                        cell.value = re.sub(rf"(?<![\w.]){re.escape(name)}\(",
                                            PREFIX[name] + name + "(", cell.value)
                    else:
                        problems.append(f"{ws.title}!{cell.coordinate}: unknown function {name}")
    return problems

wb = Workbook()
ws = wb.active
ws["A1"] = '=XLOOKUP("x",B1:B2,C1:C2)'
ws["A2"] = "=SUMM(B1:B2)"
ws["A3"] = '=IF(B1="IFS(",1,0)'
print(lint_formulas(wb))
print(lint_formulas(wb, fix=True), ws["A1"].value)

The first run reports both the missing prefix and the typo. The second run prefixes XLOOKUP and leaves only SUMM, which no automatic fix should guess at. Text inside quotes — the "IFS(" in A3 — is ignored, so string literals never trigger false alarms. Call the linter in the job's tests or just before wb.save(), and fail the run if it returns anything.

Common pitfalls

• Adding the prefix twice. _xlfn._xlfn.XLOOKUP is as broken as no prefix. The linter's lookbehind skips names already preceded by a dot.
• Assuming LibreOffice-tested files are fine in Excel, or the reverse. Both apply the prefix rule, but check workbooks in the application your readers use.
• Checking only the numbers your Python code expects. A test that compares a DataFrame against expected totals proves the logic, not the formulas stored in the workbook. Lint the stored formula text as well, because that is what the spreadsheet will parse.
• Building formulas from user data without quoting. A customer called Smith & Co breaks an unquoted comparison in more ways than one.
• Reading formulas back and expecting values. openpyxl returns formula text, prefix included; for results, open with data_only=True after the file has been calculated. Read Formula Results with openpyxl data_only explains when cached values exist.

Performance notes

The linter visits every cell once and runs one regular expression per formula, which takes well under a second for tens of thousands of formulas. On very large sheets, iterate with values_only=False only over the columns that hold formulas, or run the linter in tests against a representative output file rather than on every production run.

Conclusion

#NAME? in a formula written from Python usually means a newer function was stored without its _xlfn. prefix — add it yourself with openpyxl, or let xlsxwriter's use_future_functions do it. When the function is old or already prefixed, look for an undefined name, unquoted text, a localised function name or a typo. A short linter built on openpyxl.utils.FORMULAE catches all of them before the workbook leaves the job.

Frequently asked questions

Why does the formula work if I click into the cell and press Enter? Re-entering the cell makes the spreadsheet parse the formula text itself, and it stores the function correctly. The file your script wrote stored the name without the _xlfn. prefix, so it was not recognised until the cell was re-entered.

Which functions need the _xlfn. prefix? Functions added after the original .xlsx specification — XLOOKUP, XMATCH, TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS, MINIFS, IFNA, STDEV.S, VAR.S, the dynamic array functions and many statistical .DIST functions. A function missing from openpyxl.utils.FORMULAE is a candidate.

Why do FILTER and SORT need _xlfn._xlws. instead? They are stored with an extra _xlws. part in the file format. Writing _xlfn.FILTER without it is still unrecognised. xlsxwriter's use_future_functions option applies the right form automatically.

Can I write formulas in my local language, like SUMA or SOMME? No. The file always stores English function names with commas between arguments. Excel translates them for display in each user's language, so write =SUM(A1,A2) and a Spanish user will see =SUMA(A1;A2).

Related

• Up one level: Troubleshooting Common Python Excel Errors — other errors in generated workbooks.
• Write a Formula to an Excel Cell with openpyxl — the basics of writing formulas.
• Find #REF!, #DIV/0! and Other Formula Errors in a Workbook — locating error values after calculation.
• Recalculate Excel Formulas Without Excel in Python — checking results on a server.