Fix XLOOKUP Missing in Excel After Switching to Microsoft 365
Question details
The user cannot access the XLOOKUP function in Excel even after upgrading from an Office 2019 account to a Microsoft 365 work account.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Switching from a perpetual Office 2019 license to a Microsoft 365 subscription to access newer functions.
- Observed behavior
- The XLOOKUP function remains unavailable and is not recognized by the Excel desktop application.
Ensure you have a stable internet connection so Excel can properly sync and verify your new Microsoft 365 license credentials.
Verify Account License and Update Excel
Force Excel to recognize your new Microsoft 365 subscription instead of the older Office 2019 license.
Often, Excel retains the old license information even after you sign in with a new account. You must confirm that the active license is recognized as a Microsoft 365 subscription and then update the software.
Open Excel, click on 'File' and then select 'Account' (or 'Excel > About Excel' on Mac). Look under 'Product Information' to verify that it says 'Subscription Product: Microsoft 365'.
If it still shows Office 2019, click 'Sign Out' on all listed accounts. Restart Excel, then sign back in using exclusively your Microsoft 365 work account.
While on the Account page, click 'Update Options' next to Office Updates, and select 'Update Now' to download the latest features including XLOOKUP.
Test XLOOKUP in Excel for the Web and Repair Installation
Verify your Microsoft 365 account features online and repair the desktop app if the web version works.
Use XLOOKUP for Free in WPS Spreadsheet
Avoid complex Microsoft 365 licensing issues and account switching. WPS Office offers a free, lightweight spreadsheet application that natively supports advanced functions like XLOOKUP right out of the box.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Open your workbook: Launch WPS Spreadsheet and either create a new blank document or open your existing .xlsx file.
- 3. Apply the XLOOKUP function: Select a cell and type '=XLOOKUP(' to instantly utilize the function without worrying about subscription tiers.
Frequently Asked Questions
Why is XLOOKUP not available in Office 2019?
XLOOKUP was introduced as a dynamic array function exclusively for Microsoft 365 subscribers and later added to Office 2021. Older perpetual licenses like Office 2016 and 2019 do not receive new feature updates.
How do I force Excel to recognize my new Microsoft 365 license?
You may need to sign out of all accounts in the Excel Account settings, close all Office applications, and sign back in with your new credentials. In some cases, running the Microsoft Support and Recovery Assistant (SaRA) tool is necessary to clear old license caches.
Will my XLOOKUP formulas break if I share the file with an Office 2019 user?
Yes, if a user opens the file in Office 2019 and attempts to edit the cell or recalculate the sheet, the formula will return a #NAME? error because their version of Excel does not recognize the XLOOKUP function.
