'%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 Excel Not Recognizing XLOOKUP or TEXTJOIN Formulas
Question details
The user is unable to use newer formulas such as XLOOKUP and TEXTJOIN, which appear as nonexistent or return errors in their spreadsheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Working with spreadsheet formulas and attempting to use XLOOKUP or TEXTJOIN to retrieve, combine, or manipulate data.
- Observed behavior
- The formulas are not recognized by Excel, resulting in #NAME? errors or functions appearing as unavailable despite supposedly running a supported version like Excel 2021.
Before troubleshooting, confirm whether the cell is displaying a specific error code like #NAME?, and verify your exact Excel edition under the Account settings.
Disable Compatibility Mode and Update File Format
Workbooks saved in the older .xls format run in Compatibility Mode, which completely disables newer functions like XLOOKUP and TEXTJOIN. Converting the file restores full functionality.
When you open older workbooks in newer versions of Excel, they launch in a restricted mode to preserve backward compatibility. Upgrading the file format to the modern .xlsx standard allows all advanced calculation features to work correctly.
Click on the 'File' tab in the top-left corner of the Excel window and select 'Info' from the left sidebar.
Look for a section titled 'Compatibility Mode'. If it is visible, click on the large 'Convert' button next to it.
Confirm the prompt to upgrade the file, and save the document as an Excel Workbook (*.xlsx). Close and reopen the file to successfully use the formulas.
Check Excel Version and Apply Updates
XLOOKUP and TEXTJOIN are only available in Excel 2021, Excel for Microsoft 365, and newer versions. Ensure your current installation supports them.
Get XLOOKUP and TEXTJOIN for Free with WPS Office
If your current version of Microsoft Excel does not support XLOOKUP or TEXTJOIN, you do not need to purchase a costly software upgrade. WPS Office is a free, highly compatible alternative that natively supports modern spreadsheet formulas and features.
- 1. Download and Install: Download WPS Office for free from the official website and follow the brief installation instructions.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx or .xls file directly.
- 3. Use Advanced Formulas: Simply type =XLOOKUP or =TEXTJOIN in any cell; the functions will be instantly available and recognized without compatibility issues.
Frequently Asked Questions
Why am I getting a #NAME? error when using XLOOKUP?
The #NAME? error happens when the spreadsheet program cannot recognize the function name. This usually means your version of Excel does not support XLOOKUP, you are working in Compatibility Mode, or there is a spelling mistake in the formula name.
Is XLOOKUP available in Excel 2016 or 2019?
No, XLOOKUP was introduced with Microsoft 365 and is only available in Microsoft 365, Excel 2021, and newer versions. For older versions, you must use a combination of INDEX and MATCH instead, or use an alternative like WPS Office.
How do I know if my workbook is in Compatibility Mode?
Look at the very top title bar of your Excel window. If you see the text '[Compatibility Mode]' displayed directly next to your document's file name, the file is saved in an older format (.xls) which restricts newer features.
How do I turn off Compatibility Mode in Excel?
To turn off Compatibility Mode, go to File > Info, click on 'Convert', and save your document as a standard modern Excel Workbook (.xlsx format).
