Tell AI which Excel you have before asking for a formula
What the Reel says
Tell AI which Excel you have before asking for a formula
I asked GPT-5.5 for a price lookup formula.
It wrote XLOOKUP, a newer function.
Excel 2016 and 2019 don't have it.
There, it shows a #NAME? error.
Once I named 2016, it switched to VLOOKUP.
Not sure? Look under File, then Account.
Start each prompt with your version.
Sources and assumptions
- Microsoft Support: XLOOKUP function: "XLOOKUP is not available in Excel 2016 and Excel 2019." The page lists Excel for Microsoft 365, Excel 2024 and Excel 2021 (Windows and Mac) among the versions that have it. (checked 2026-11-07)
- Microsoft Support: Detect errors in formulas (reached from the en-gb '_xlfn prefix' support link): "#NAME? error is displayed when Excel does not recognize text in a formula", for example the name of a function. So Excel 2016 shows #NAME? for XLOOKUP, a function it doesn't have. (checked 2026-11-07)
- Microsoft Q&A: an _xlfn prefix is displayed in front of a function (Independent Advisor answer, 2025-12-04): Supporting source only (a community advisor, not Microsoft's own documentation): the _xlfn. prefix means the workbook uses a function your Excel version doesn't support, usually one from Microsoft 365. (checked 2026-11-07)
- Microsoft Support: IFERROR function: IFERROR is available in Excel 2016 and later. It catches "#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!", so it hides every error, not only missing codes. (checked 2026-11-07)
- Microsoft Support: What version of Office am I using?: How to find your version. Windows: open Excel, select Account, and look under Product Information (About Excel shows more). Mac: Excel menu, then About Excel. (checked 2026-11-07)
Assumptions:
- The tests ran on GPT-5.5 through the OpenAI API, labeled "GPT-5.5 (OpenAI API)", Nov 2026. The idea planned a Claude test, but both Claude runs failed to start in this research environment (claude -p command error), so the post must not claim a Claude result.
- The sample sheets are fictional: an Orders sheet with codes from A2 down, and a Prices sheet with codes in A2:A200 and prices in C2:C200.
- The vague prompt ran twice (before-gpt-2 and before-gpt-3) and returned the same XLOOKUP formula both times. A third, wordier vague prompt without "Just the formula" (before-gpt) gave VLOOKUP first and offered XLOOKUP only "if you have Excel 365 or Excel 2021". So the model doesn't always default to XLOOKUP. The short, formula-only request is the one that did.
- The #NAME? result in Excel 2016 is drawn from Microsoft's documentation (XLOOKUP isn't available in 2016, and Excel shows #NAME? for function names it doesn't recognize). We didn't open the formula in a real Excel 2016.


