Formulas not supported anymore

Dear @Marcos S. Mendez, CPA,

Thank you for reaching out I understand how frustrating it is to see formulas that worked previously stop functioning, especially in important Excel files.

The _xlfn. prefix indicates that Excel considers the function unsupported in the version currently running. In your case, XLOOKUP is being treated as unavailable, which causes formulas to stop working and may return #NAME? errors.

For reference: Issue: An _xlfn. prefix is displayed in front of a formula

This behavior occurs when the file is opened in a version or build of Excel that does not currently recognize XLOOKUP, or Excel has regressed to an older build or channel (for example, after an update, account sign‑in change, or repair), even though the product name still shows Office Professional Plus 2021.

Additionally, your purchase from third party is valid, and this issue is not caused by the license being illegitimate or expired. Office 2021 is a perpetual (one‑time purchase) license, but advanced functions such as XLOOKUP are tied to specific feature builds. If Excel temporarily falls back to a build that does not include the function, the formula will break.

In this case, please kindly check some steps below:

Step 1: Confirm the actual Excel product in use

• In Excel > Go to File > Account > About Excel
• You should explicitly see Microsoft Office Professional Plus 2021
• If you see something else (e.g. Excel 2019, Unlicensed, Viewer, Microsoft 365 Apps (deactivated)), that explains the issue.

Step 2: Check activation status

Still in File > Account, confirm Product is Activated and no warning like “Subscription expired” or “Unlicensed Product”.

Step 3: Repair Office Application: You can refer to Repair an Office application - Microsoft Support and then check the result.

Step 4: Contact a Reseller

If your Office subscriptions are managed through a federated partner or reseller and the Global Admin cannot open a service request, they should contact the reseller’s support provider. The reseller can assist with initiating the request and coordinating with the Office support team.     

Please notice and accept my apologies that as moderators in the community, we are not supported for the advanced info for Microsoft products. To help you send your feedback and requirement to the related development team, we have redirected you to the correct path. 

Please feel free to reply below if you have any update or further concern. Your patience and understanding are highly appreciated.

I am looking forward to your response!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

The _xlfn. prefix and #NAME? error indicate that Excel no longer recognizes XLOOKUP as a supported function in the version currently running.

From the symptoms described, there are two likely causes:

1. Excel is running in a mode or version that does not support XLOOKUP
   • When a workbook that uses newer functions is opened in an Excel version that does not support them, Excel automatically prefixes those functions with _xlfn. and returns #NAME?.
   • This behavior is documented for unsupported functions such as IFERROR in older Excel versions, and the same mechanism applies to XLOOKUP.
   • If Excel has switched to an older build, a different installed edition, or a reduced‑functionality mode that does not include XLOOKUP, all existing XLOOKUP formulas will appear as =_xlfn.XLOOKUP(...) and break.
2. Compatibility or file‑format downgrade
   • If the workbook was saved in a compatibility format (for example, as an older .xls file) or opened in an environment that enforces compatibility with older Excel versions, newer functions are treated as unsupported.
   • In such cases, Excel flags them in the Compatibility Checker as “functions not available in earlier versions” and may add the _xlfn prefix.

What this means for the current installation

• XLOOKUP is a newer function. When Excel runs in a version or mode that does not support it, formulas using XLOOKUP are treated as unsupported and converted to _xlfn.XLOOKUP, resulting in #NAME?.
• The change “since today” strongly suggests that something changed in the installed Office build or activation state, not in the workbook itself.

How to proceed

1. Confirm the exact Excel version and edition
   • In Excel, check the version/build and product information to ensure it is still the expected Office Professional Plus 2021 installation and not an older or different edition.
2. Restore a version of Excel that supports XLOOKUP
   • If Excel has reverted to an edition or mode that does not include XLOOKUP, reinstalling or repairing Office so that the full Excel 2021 feature set (including XLOOKUP) is available again is required.
3. If forced to work without XLOOKUP
   • If for any reason Excel cannot be restored to a build that supports XLOOKUP, unsupported functions must be removed or replaced with functions that are supported in the active Excel version to avoid _xlfn. and #NAME? errors.

References:

• Issue: An _xlfn. prefix is displayed in front of a formula
• Use Excel with earlier versions of Excel
• Formula compatibility issues in Excel
• XLOOKUP function
• What's new in Excel 2021 for Windows
• Overview of formulas in Excel
• How to avoid broken formulas in Excel