'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Missing UNIQUE and XLOOKUP Functions in Excel 2021 or 365
Question details
The user is attempting to use the UNIQUE and XLOOKUP functions but finds them missing or returning a #NAME? error, despite using compatible versions like Excel 2021 or Microsoft 365.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying advanced dynamic array and lookup functions for data processing and analysis.
- Observed behavior
- Excel fails to recognize the functions, which indicates an outdated software build or a disrupted Microsoft 365 subscription sync.
Verify your active Microsoft account subscription status and confirm your installed version under File > Account. Older standalone versions like Excel 2016 or 2019 do not support these dynamic array functions.
Refresh Your Microsoft Office Account Sign-in
Signing out and signing back into your Microsoft account can refresh your subscription status and unlock missing Microsoft 365 exclusive functions.
Microsoft 365 periodically checks your license status in the background. If the background sync fails or your license gets temporarily unverified, dynamic array functions like XLOOKUP and UNIQUE may be hidden. Forcing a manual refresh of your account connection often resolves this instantly.
Open Excel, click on 'File' in the top-left ribbon, and select 'Account' at the bottom left.
Under the 'User Information' section, click the 'Sign out' button and confirm your choice in the prompt.
Close Microsoft Excel and any other open Microsoft Office applications completely to ensure the session ends.
Reopen Excel, navigate back to 'File' > 'Account', click 'Sign In', and enter your active Microsoft 365 credentials.
Check For and Install Office Updates
Outdated builds of Excel 365 or 2021 might not have received the dynamic array functions update yet, requiring a manual update.
Repair or Reinstall Microsoft Office
If refreshing and updating fail, the Office installation files might be corrupted and require a clean reinstall to restore missing function libraries.
Use WPS Spreadsheet for Reliable Advanced Functions
If Microsoft Excel continues to block access to dynamic array functions due to recurring subscription sync bugs, try WPS Office. WPS Spreadsheet provides native, reliable support for XLOOKUP, UNIQUE, and other modern functions in a highly compatible environment.
- 1. Download and Install: Visit the WPS Office website to download the free installer and complete the quick installation process.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 3. Use XLOOKUP and UNIQUE: Type your advanced formulas exactly as you would in Excel to enjoy seamless data analysis without subscription errors.
Frequently Asked Questions
Why is my XLOOKUP formula returning a #NAME? error?
A #NAME? error indicates that Excel does not recognize the function name. This happens if you are using an older version (like Excel 2016 or 2019), or if your Microsoft 365 subscription has temporarily lost sync and hidden the dynamic array functions.
Can I add UNIQUE and XLOOKUP to Excel 2019?
No. The UNIQUE and XLOOKUP dynamic array functions are strictly available only in Microsoft 365, Excel 2021, and newer versions. They cannot be patched into Excel 2019 or earlier standalone editions.
How do I check exactly which version of Excel I am running?
Open Excel, click on the 'File' menu, and select 'Account'. Look under the 'Product Information' section on the right side of the screen to see your specific version and build number.
Will my XLOOKUP file work if I send it to someone with an older version of Excel?
If you share a workbook containing XLOOKUP with someone using an older version (e.g., Excel 2016), they will see a #NAME? error in the cells containing that formula, as their software lacks the capability to evaluate it.
