'%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)
How to Fix XLOOKUP Not Working in Microsoft Excel
Question details
The user is unable to use the XLOOKUP function in Excel because it is either unavailable, unrecognized, or returns an error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to search for data across an array or range using the XLOOKUP formula.
- Observed behavior
- The XLOOKUP function is missing from the function library, returns a #NAME? error, or fails to compute, even though older functions like VLOOKUP continue to work.
Verify your exact Microsoft Office version, as XLOOKUP is exclusively available in Microsoft 365, Excel 2021, and newer editions. Standalone versions like Excel 2019 and Excel 2016 do not support this function.
Verify Excel Version and Install Updates
Confirm that your Excel version supports XLOOKUP and apply the latest Office updates to restore missing features.
If you are a Microsoft 365 subscriber but XLOOKUP is missing, your Office client might be outdated or on a delayed update channel.
Open Excel, click on 'File' in the top-left corner, and select 'Account' at the bottom of the left sidebar.
Look under 'Product Information' to confirm you have Microsoft 365 or Office 2021/2024 installed.
Click the 'Update Options' button next to Office Updates and select 'Update Now' from the dropdown menu to download pending feature updates.
Run an Online Repair for Microsoft Office
Repair your Office installation to fix corrupted files that might be causing built-in functions to disappear.
Use INDEX and MATCH as an Alternative
Use the highly compatible INDEX and MATCH formula combination if upgrading your Excel version is not currently possible.
Try WPS Office for Seamless Spreadsheet Management
If you are tired of dealing with unsupported functions and expensive Microsoft Excel upgrades, try WPS Office. It is a free, lightweight, and highly compatible alternative that natively supports modern spreadsheet formulas.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file without losing any formatting.
- 3. Use Advanced Functions: Easily write modern formulas and manage your complex data sets using the built-in function library.
Frequently Asked Questions
Why is my XLOOKUP returning a #NAME? error?
The #NAME? error typically occurs if you misspell the function name (e.g., typing XLOOOKUP) or if you open the workbook in an older version of Excel that does not support the XLOOKUP function.
Can I use XLOOKUP in Excel 2019 or Excel 2016?
No, XLOOKUP is not natively supported in Excel 2019, 2016, or older perpetual versions. You must use Microsoft 365, Excel 2021/2024, or rely on alternatives like VLOOKUP or the INDEX and MATCH combination.
How do I fix the #N/A error in an XLOOKUP formula?
An #N/A error means the lookup value was not found in the search array. You can fix this elegantly by utilizing the built-in [if_not_found] argument in your XLOOKUP formula, such as =XLOOKUP(A1, B:B, C:C, "Not Found").
