Why a formula that worked yesterday shows #NAME? today
Nothing about the formula changed. What changed is which bytes got written to xl/worksheets/sheet1.xml the last time the file was saved.
The answer
Excel does not store every function name literally. Anything introduced after the original 2007 formula grammar — LET, LAMBDA, XLOOKUP, UNIQUE, SORT, SEQUENCE, FILTER, MAP, the .S/.P statistics variants — gets written to the underlying XML with a namespace prefix, _xlfn., so older Excel builds that don't recognize the function can at least fail predictably instead of guessing. Excel strips the prefix in the formula bar, so you never see it — you just see =XLOOKUP(A1,B:B,C:C) and a correct answer.
Two functions need a second layer on top of that: FILTER and SORT only make sense on a worksheet (not, say, inside a name manager formula), so Excel marks them _xlfn._xlws.FILTER and _xlfn._xlws.SORT — a second, two-function namespace nested inside the first. Every other modern function uses plain _xlfn. alone.
If whatever last wrote the file — a script, an internal tool, a hand-rolled exporter — emits the bare function name instead of the prefixed one, Excel opens the file, doesn't recognize the raw token, and shows #NAME? in every cell that uses it. The formula bar shows nothing wrong; the fix is invisible until you know to look for it.
What's actually on disk vs. what you see
| Formula bar shows | xl/worksheets/sheet1.xml stores |
|---|---|
LET(x,SUM(A1:A3),x*2) | _xlfn.LET(x,SUM(A1:A3),x*2) |
MAP(A1:A3,LAMBDA(v,v*2)) | _xlfn.MAP(A1:A3,_xlfn.LAMBDA(v,v*2)) |
UNIQUE(A1:A3) | _xlfn.UNIQUE(A1:A3) |
SEQUENCE(3) | _xlfn.SEQUENCE(3) |
XLOOKUP(1,A1:A3,A1:A3) | _xlfn.XLOOKUP(1,A1:A3,A1:A3) |
STDEV.S(A1:A3) | _xlfn.STDEV.S(A1:A3) |
FILTER(A1:A3,A1:A3) | _xlfn._xlws.FILTER(A1:A3,A1:A3) |
SORT(A1:A3) | _xlfn._xlws.SORT(A1:A3) |
Every row above is one of the four cases our writer round-trips on every commit — table reproduced from core/tests/integration.rs, function basis_authored_modern_functions_gain_the_correct_xlfn_prefix_on_write.
Reproduce it
Take any file with a nested-function formula like this one and inspect the raw XML:
$ unzip -p model.xlsx xl/worksheets/sheet1.xml | grep -o '<f>[^<]*</f>' | head -3 <f>_xlfn.XLOOKUP(A2,Rates!A:A,Rates!C:C)</f> <f>_xlfn.LET(x,SUM(B2:B10),x*1.08)</f> <f>SUM(C2:C10)</f> <- an ordinary function, no prefix needed
The bug reproduces the moment a tool rewrites that middle line back out as <f>LET(x,SUM(B2:B10),x*1.08)</f> — syntactically identical, semantically identical, and broken the instant Excel opens it.
The prefixing also has to be idempotent: a file can already carry a wrong prefix — _xlfn.FILTER without the _xlws half, or the reverse on a function that doesn't need it — and re-saving has to repair it rather than compounding it. That's a separate regression test (xlfn_prefixing_is_idempotent_and_repairs_wrong_prefixes) alongside the write-path one, both passing against the current build.
Excel vs. most tools
Excel
Writes and reads the exact _xlfn./_xlfn._xlws. namespace per function, translating invisibly in the UI. It has to — it invented the mechanism to stay compatible with itself across versions.
Naive writers
A tool that treats formula text as an opaque string round-trips the bare name it was given (or the name a user typed) without knowing which functions are new enough to need the marker — correct until the exact moment someone uses a function newer than the writer's assumptions.
Basis carries a table-driven prefix map — plain _xlfn. for the general case, _xlfn._xlws. for the two worksheet-only functions — applied on every write and repaired on every read-modify-write, even when the source file already had it wrong.
How common is this in the wild
Across the two public corpora we've audited end to end — FUSE (10,703 real .xlsx files, a 2014 web crawl) and the Enron spreadsheets (15,871 files, collapsed 2001) — _xlfn.-prefixed formulas appear in only 2 files total. That's expected, not reassuring: both corpora predate mainstream adoption of LET, XLOOKUP and dynamic arrays. Every workbook built in Excel 365 today that uses any of them needs this exact mechanism to survive a round-trip, and neither corpus can tell you that, because neither corpus is that old — it's too young.
Building something that writes .xlsx? Get the prefix table, the idempotent repair, and the rest of the writer for free — it's zero-dependency Rust compiled to a 187 KB wasm module or a native library.