openpyxl writes XLOOKUP; Excel and LibreOffice read _xlfn.XLOOKUP

You wrote the formula in Python, the script ran without complaint, and then you opened the workbook and the cell says #NAME?. The formula bar looks right. XLOOKUP exists in the application you are looking at. Retype the same formula by hand into the same cell and it computes. Nothing about the function is wrong — the problem is the name your file uses to ask for it.

The short version: OOXML stores post-2007 functions under a prefixed token, and openpyxl does not add the prefix for you. It writes exactly the string you assign. So ws["D1"] = '=XLOOKUP(...)' puts the literal text XLOOKUP(...) into the sheet XML, and every engine that reads that file looks up a function called XLOOKUP in the pre-2007 name table, does not find one, and reports #NAME?. Writing =_xlfn.XLOOKUP(...) instead is the whole fix.

What openpyxl actually writes

We built one workbook with openpyxl 3.1.5 on 2026-09-12: A1="k1", B1=10, A2="k2", B2=20, and three formulas in column D. Unzipping the result and reading xl/worksheets/sheet1.xml shows this:

Two things to take from that XML, and both matter.

openpyxl adds no prefix. The formula text is stored character for character as you gave it, minus the leading =. There is no name table, no translation step, no list of known functions. Whatever you type is what the file says. That is a reasonable design for a library that writes XML, and it means the OOXML naming rules are yours to get right.

openpyxl writes no cached value. The <v> element is empty for all three cells. A cell in a .xlsx normally carries both the formula and the last value the writing application computed for it; openpyxl computes nothing, so there is no value to cache. Whatever opens the file has to evaluate every formula itself. That is why this failure shows up the instant you open the workbook rather than the next time something recalculates — and it is also why the file is a clean test: nothing can be showing you a stale number from another engine.

What each engine does with that file

We opened the same workbook — one file, three cells — in every engine we execute. VLOOKUP in D3 is the control: it is a pre-2007 function, it is stored under its own bare name, and it should work everywhere. Values below are from results/probes/xlfn-openpyxl-2026-09-12/README.md.

The plain spelling fails in every engine we ran, including the one that invented XLOOKUP. The prefixed spelling works everywhere except LibreOffice 24.2, and the control works everywhere, which is what tells you the file itself is fine and the engines are all reading it.

What Excel for the web showed on screen

Two details from the same 2026-09-12 Excel for the web session are worth recording, because they are what you would actually see and they are easy to misread.

The prefix is hidden on display, and the plain name is rewritten. With D2 selected — the cell we stored as _xlfn.XLOOKUP — the formula bar displayed =XLOOKUP("k2",A1:A2,B1:B2). The prefix does not appear in the UI at all; it is a file-format detail, not something a user ever types. With D1 selected — the cell we stored under the plain name — the formula bar displayed =@XLOOKUP("k2",A1:A2,B1:B2). Excel parsed the unprefixed name as an unknown legacy name and inserted the implicit-intersection @ operator in front of it. So the broken cell and the working cell both show you something that is not what your Python wrote, in opposite directions, and neither formula bar is evidence about what is in the file. Only the XML is.

Excel offered to repair the file, and we declined. On open, Excel for the web showed an Update Workbook for Compatibility prompt reading “This workbook requires updates to function optimally in Excel”, and its Workbook compatibility pane reported “1 cells with incompatible formulas” with a Fix now button. One cell — D1, the plain-name one. We chose Skip, so the file was not modified and the results in the table above are of the file as openpyxl wrote it. We did not test what Fix now would do, so we cannot tell you whether it rewrites the token, and you should not read the prompt’s existence as a promise that it does.

The fix

Write the prefixed token from openpyxl:

That is the entire change, and three consequences of it are worth stating explicitly.

Nobody sees the prefix. Excel strips it for display — measured above, on 2026-09-12: the cell we stored as _xlfn.XLOOKUP showed as =XLOOKUP(...) in the formula bar. Your users open the file and see the formula they expect. The prefix lives in the XML and nowhere else.

Your cells still have no cached values, and that is fine. openpyxl caches nothing either way, so both Excel and LibreOffice recalculate the sheet on open and fill the values in. You do not need to compute anything in Python to make this work. It does follow from the empty <v> elements above that any tool reading the values out of your file without recalculating it has nothing to read — there is no cached number in the file yet — until some spreadsheet application has opened and saved it.

The prefix is per-function, and not always just _xlfn. Some functions take a second prefix on top of it: in our corpus FILTER and SORT are stored as _xlfn._xlws.FILTER and _xlfn._xlws.SORT, not _xlfn.FILTER and _xlfn.SORT. Guessing is not safe; the next table is the list we can vouch for.

Which token for which function

These are the exact tokens our harness stores for each function, taken from the formula_stored_xlsx field of results/excel-web.json, together with how the cases carrying those tokens came out when Excel for the web recalculated them on 2026-09-01. “All N cases evaluated” means every case returned a value or a computed error such as #N/A from the function itself — none returned #NAME?, which is the failure this page is about.

Three honest notes on that table.

The _xlfn.LAMBDA token appears in results/excel-web.json only nested inside our two BYROW/BYCOL cases, and those two cases came back with no value at all, so we have a confirmed spelling and no executed verdict behind it. There is no _xlfn.LET anywhere in that file, so we list no token for LET; we are not going to guess one.

These tokens are what our harness wrote and Excel for the web read back and computed. That is direct evidence the reader accepts them — which is exactly what an openpyxl author needs. It is not evidence about what token Excel itself would choose when saving a file, because every workbook in our corpus was written by us, not by Excel.

The list is short because it is limited to functions we have executed cases for. For anything not on it, the safe move is to write the formula by hand in Excel, save, unzip, and read your own <f> element — which is the same measurement we made, just on your function.

A warning about Google Sheets

Do not assume the prefixed token is universally correct, because for Google Sheets we have evidence pointing the other way. In our published corpus the XLOOKUP_exact_match case (=XLOOKUP("b",A1:A3,B1:B3), expected 2) ran like this:

All three agree on the answer, but read the middle column: the Sheets run got its 2 from a file storing the plain name. We have not executed _xlfn.XLOOKUP in Google Sheets, so this page cannot tell you what Sheets does with the prefixed spelling of this function. What we can tell you is why we wrote the plain name for that run: Google’s importer does not map every OOXML storage prefix, which is documented on our Google Sheets execution caveats.

The practical consequence for an openpyxl script: the token that makes a file open correctly is a property of the engine that will open it, not of the function. If your output is going to Excel or LibreOffice, write _xlfn.. If it is going to Google Drive to be imported by Sheets, you are in territory we have only partially measured, and the safe path is to test your own file rather than trust a rule.

Related reading and tools

The mechanism here — a function that exists but cannot be named by the file — is a whole class of problem, not one function. The sibling guide Your function exists, but your file cannot say so works through it from the LibreOffice side: eight functions LibreOffice implements but cannot be told about, eleven of its own legacy names its exporter erases, and a token change between 24.2 and 24.8 that makes a LibreOffice-written file fail to open in LibreOffice. The per-engine support matrix for this function is on the XLOOKUP function page, and how we prove a recalculation actually happened is on our methodology page.

If you want per-cell ground truth for a whole workbook rather than per-function verdicts, our free offline checker scripts/xlsx_recalc_diff.py diffs the values Excel saved inside a .xlsx against a forced LibreOffice recalculation of the same file, on your own machine, with nothing uploaded. Note the shape of that tool before you reach for it here: it compares against Excel’s cached values, so it wants a workbook Excel has saved. A file fresh out of openpyxl has no cached values for it to compare against — open and save it in Excel first, or use it on the file your users send back rather than on your generator’s output.

Honest limits

Desktop Excel is not executed anywhere on this page. We do not run it. Every Excel value above is Excel for the web, which is a separate implementation with its own calculation engine. We also quote no vendor documentation here: we tried to fetch Microsoft’s support article on the _xlfn. prefix and did not get the article’s text back in a form we could quote exactly, and this site does not paraphrase documentation from memory. So the claim “Excel needs the prefix” rests on our own 2026-09-12 measurement of Excel for the web, and on nothing else.

The probe is one file with three cells. One key type, one lookup, one workbook, written by one version of openpyxl (3.1.5) on 2026-09-12. It is enough to establish which spelling each engine reads, because that is a property of the name table rather than of the arguments, but it is not a survey. Other openpyxl versions, other functions, and formulas with more complex arguments are untested by this probe.

The Excel for the web numbers of 2026-09-12 were read on screen. That run uploaded the file to OneDrive, opened it in Excel for the web, and read the values in the cells; it did not download the recalculated file for machine readback the way our main Excel-for-the-web corpus run does. The 2026-09-01 corpus values quoted in the token table and the Sheets comparison come from the normal download-and-read-back pipeline.

Four LibreOffice builds, not a bisection. 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 are the only builds we ran. “XLOOKUP works from 24.8” means those builds behaved that way; we have not identified the exact release that changed it.

This probe file did not go through Google Sheets, and the _xlfn.XLOOKUP spelling has no execution in our corpus in Sheets by us for this function. The Sheets row in the corpus table is the plain-name run of 2026-08-29.

We declined the repair. Excel for the web offered to update the workbook for compatibility and we chose Skip. What its Fix now button does to a plain-name XLOOKUP cell is untested by us.

Check before you migrate